How to append a text string to all cells beginning with a number

[Paws7]

Hi all,
I have a dataset of 50,000 historical addresses (very messy). Some of these have the structure:

|4 example street|

I know that if the address begins with a number it belongs to a particular city. I want to append this to each of these cells to get:

|4 example street city|

I am thinking an "if then else" but I'm not sure how to construct this.

Can anyone help?

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this work for you?

=A1&IF(ISNUMBER(LEFT(A1,1)+0)," city","")

 

[Paws7]

Goodness me, how fantastic!
That's a winner.
Now, there are also some cells that just have
|Street|
That I want to append the city to also. Could you point me in the right direction of how I would write a similar statement for this? So if cell contains "street" then append.
Much appreciated!

 

[Paws7]

Should read
|something street|
where "something" is not always the same so don't think I can use LEFT on this

 

[Andrew Poulsom]

How about?

=A1&IF(OR(ISNUMBER(LEFT(A1,1)+0),ISNUMBER(FIND("street",A1)))," city","")

 

[Paws7]

Thank you very much!

That has done the trick, again. And it also helps me to apply in other ways so thanks again for sharing.

Have a great day.