Separate text string into 2 columns

[FireBott]

Hi. I have looked through this forum and can't find anything to help me. I have 1 column that contains city names followed by a 5 digit code. I need to separate the 5 digit code from the city name. There is no space between the city name and number. Some of the city names have spaces. Is there a way to have a formula too look at the string, find the first numeric character and separate it.

 

[sprigelz]

Try using this.

Text String	Town	Zip
acrp johnstown00144	=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))	=RIGHT(A1,5)

The zip formula assumes that the Zip Code is always 5 characters long.

 

[Fluff]

Another option

=LEFT(A2,LEN(A2)-5)

and

=TEXT(RIGHT(A2,5),"00000")

 

[FireBott]

Another option

=LEFT(A2,LEN(A2)-5)

and

=TEXT(RIGHT(A2,5),"00000")

Thank you. That worked great.

 

[Fluff]

Glad we could help & thanks for the feedback.