Data separation by using formula FIND, MID

Hi,

One or two examples is often woefully insufficient to support this type of query.

Please give as much information about where the extraction in each string is to take place. For example, in your (one) example, the desired extraction (India) is the last word in the string - will this always be the case? Will it always be a single word, or possibly more, e.g. Sri Lanka? In either case, will it always be preceded by a comma?

Regards

Hi XOR LX , thanks for reply
The text in the cell A1 is actually address , here your query , yes country is not unique . please treat the address variation , India,Sri Lanka, Bangladesh etc , & like wise address will be change , I mean all are changeable but they are following a pattern which is same .

it always be preceded by a comma?

yes it is preceded by a comma.

Thanks. And the answer to my other question: is the desired extraction always the last word (or words) in the string?

Regards

I'm afraid you still haven't answered my last question.

Regards

In C1

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

in B1

=LEFT(A1,LEN(A1)-2-LEN(C1))

	A	B	C
1	97 iscon greens bopal, ahmedabad, 380058, India	97 iscon greens bopal, ahmedabad, 380058	India

Sorry I din't read that part

here it is:

In C1

=TRIM(RIGHT(SUBSTITUTE(A1,"#",REPT(" ",255)),255))

In B1

=LEFT(A1,LEN(A1)-4-LEN(C1))

	A	B	C
1	Sunday, February 9, 2014 at 08:49:01 · #510497609	Sunday, February 9, 2014 at 08:49:01	510497609

Originally Posted by nur2544

I will be more happy if I get my 2nd part issues answer also form you that is like below

Maybe you try to replace "," inside SUBSTITUTE to "#"

You're welcome and thank you for your feedback!