formula help please

[Jen Legg]

I am transcibing 1891 census in Excel 2002 csv and would like to be able to enter a formula that would allow the following.

where column f is a number, column g automaticaly adds the street name. ie London street.

blank rows between

20 london st
20a london st

20b london st


21 london st

Any help most appreciated
Jen

 

[Katherine Coombs]

Jen,

Excel can certainly automatically return data to one column based on the
content of a different column, however is this a lookup function? or do you
always want the same street name added in?

I guess what I'm asking is, how does Excel know which street name to put in?

Cheers,
Katherine

I am transcibing 1891 census in Excel 2002 csv and would like to be able

to enter a formula that would allow the following.

 

[Jen Legg]

hi Katherine
it's the same street for long sections at a time. I can alter the formula as
the street changes.
regards
Jen

 

[Max]

Assuming you have in:

F2: 20 (the street nos)
G2: a (the alphas, if any)

J2: London Street (say)

Try putting in H2 : =IF(ISNUMBER(F2),F2&G2&" "&$J$2,"")
copy down col H

 

[Katherine Coombs]

ok, select the cells that you want to put the same name in. Then type the
name and press Control + Enter and it will populate all of the selected
cells with the text that you just typed.

HTH,
katherine

 

[Jen Legg]

Thanks Katherine,
Since there are hundred(sometimes) enteries it will be just as easy to copy
and paste.

I was hoping to have a formula that worked along the lines of:
Example
If cell in F is a number, column G is London Street

Living in Hope
Jen

 

[Jen Legg]

Thanks Max,
I think this will work!
Regards
Jen

Assuming you have in:

F2: 20 (the street nos)
G2: a (the alphas, if any)

J2: London Street (say)

Try putting in H2 : =IF(ISNUMBER(F2),F2&G2&" "&$J$2,"")
copy down col H

formula

 

[Jen Legg]

Katherine,
I take it back! your suggestion actually does the job very well.
Thanks
Jen

 

[Max]

you're welcome, Jen!