help with a formula

[manikm]

Hi guys,

I need to populate a row with data from an corresponding rows data, WITH additional numbers tagged onto the end - is this possible?

E.g. From COLUMNB (fig.1), i need to generate EARTH123 (or any number) and filled in into COLUMNA, all the way down (see fig2)

fig.1

COLUMNA COLUMNB

EARTH
MARS
PLUTO
ENGLAND
SCOTLAND
WALES

fig.2

COLUMNA COLUMNB

EARTH123 EARTH
MARS456 MARS
PLUTO654 PLUTO
ENGLAND23 ENGLAND
SCOTLAND23 SCOTLAND
WALES4544 WALES

THANKS!!!!

 

[dhulbert]

You cold put th enumber sin a third column then in column a just use

=B1&C1

to get

EARTH123 EARTH 123



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[manikm]

thanks for the quick reply.

id like the numbers to be random, if possible??

 

[SkipVought]

Then use the RAND() function

Skip,
[sub]
[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it![/sub]

 

[dhulbert]

Ok Put the following in column C

=FIXED(RAND()*1000,0)

Copy it down adn then each tim eyou press F9 the values will be re-calculated.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[manikm]

Thanks guys,

I used =B1&TRUNC(RAND()*1000) in the end, works fine.

One more question - is it possible to chop off some of the chars in Column B when populating Column A ?

EG.

So EARTH will become EAR124 ?
ENGLAND will become ENGL343 ?

also, once the column is populated (its a one off thing)

How can i stop it evaluating and changing the numbers after the population?

that would be the icing on the cake!

THANK YOU!

 

[HarleyQuinn]

You can use LEFT() to remove some of the characters from the value in Column B.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[manikm]

so where do i put that in my:

=B1&TRUNC(RAND()*1000)


thanks?!

 

[HarleyQuinn]

Try sometihng like:

=LEFT(B1,<num of characters you wish to see>)TRUNC(RAND()*1000)

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[manikm]

sorted!

works a treat!!!

thanks!!!

 

[AnotherHiggins]

also, once the column is populated (its a one off thing) How can i stop it evaluating and changing the numbers after the population?

To stop it from evaluating, copy the column containing the formula, go to Edit > Paste Special > Values.

[tt]_____
[blue]-John[/blue][/tt]

"Patriotism is your conviction that this country is superior to all other countries because you were born in it."
[tab]-George Bernard Shaw

Help us help you. Please read FAQ181-2886 before posting.

 

[manikm]

thanks that worked a treat!!!

THANKS TO ALL!

 

[manikm]

Morning guys,

I have a further question on this;

Is it possible to increment the next row by one each time

So, in a new column i want to take the info from coulmn B:

EARTH
MARS

and in the new column it will insert EARTH1 - then MARS2 and so on....my aim is to to generate unique ID's from COLUMN B

THANKS again!!!!

 

[Thomasdelta]

How about have column C with a sequential number, and then including that in your formula.

I read a book once called '101 Things to do in Binary'.
It was the best 5 things I ever read.

 

[earthandfire]

You've got your text in column B so in column A you could possibly use something like:

=LEFT(B1,3)&CELL("Row",B1)


Hope this helps.

 

[manikm]

I ended up using

=B6&(ROW()-5)


Its worked a treat - many thanks for your replies.