Can text formulas check text right-to-left?

[maxedison]

Is there any way to check a text string from right to left?

I am trying to break up addresses that appear in one cell into a number of cells. For example, take this city, state/province, zip that appears in one cell:

Nanjing, Jiangsu 210036

I need to break that up into City, State/Priovince, and Zip. HOWEVER, other records may have two words for the city or two for the province. Also, some Zip codes begin with letters. Therefore, I cannot use formulas that analyze the text from left-to-right based on spaces, or based on when a number is finally encountered. I can extract the city name easily enough because it is always followed by a comma, but I can't figure out how to extract the State/Province info separately from the Zip in a way that takes into account the various ways that they may appear (as described earlier in this paragraph).

Being able to search the cell from right-to-left would very easily solve my problem, as I could consider the Zip Code to be all those letters and numbers that appear before the first space. Is there a way to do this?

Thanks!

[sweep]

Hi,

Try this

http://www.mrexcel.com/archive2/25000/28430.htm

[maxedison]

Sweep,

I had come across that page while looking for an answer, but I don't really understand the formulas that are recommended. Could you offer a bit of an explanation?

At the moment, I have solved what I need in a very very inefficient way. By using CONCATENATE with a long series of IF functions, I am able to output the text backwards. For example:

CONCATENATE(IF(NOT(ISERROR(MID(A1,LEN(A1)-0,1))),MID(A1,LEN(A1)-0,1),""), IF(NOT(ISERROR(MID(A1,LEN(A1)-1,1))),MID(A1,LEN(A1)-1,1),..............

The problem with this formula, as you can probably see, is that I need a new IF function for each additional letter in the cell that I am referencing. So, to be sure that I get each letter, I have 93 total IF functions. From here, I can then use a FIND function in the typical left-to-right manner, since this will output the same number as it would have had it been used on reference cell A1 from right-to-left.

While this works for the specific task I need it for, I would love to know a better way to achieve this. So if you could explain what that page you linked to says, I'd be very grateful.

Thanks!

[sweep]

Hi,

I've attached a sample spreadsheet that splits the functions up a little.

[daddylonglegs]

Slightly different approach

If address is in A1 then to get all data before the comma use this formula in B1

=LEFT(A1,FIND(",",A1&",")-1)

then in D1 to extract the zip (asuming zip is no more than 30 characters)

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),30))

then in C1 use the data in B1 and D1 to return the rest, i.e.

=TRIM(SUBSTITUTE(MID(A1,LEN(B1)+2,255),D1,"",1))

Note: if A1 is blank the above formulas will all return blanks

[maxedison]

Thanks for the help guys!

[SMO]

Hi,

I've attached a sample spreadsheet that splits the functions up a little.

Excellent! Thanks for this.