Separate last two words and put in a separate column

[n.aditya.k]

Hi All,

I am new to this forum. Also, I am thankful to this forum. I was not able to find apt solution for the problem posted. e.g. "Tanmayi Ispat Pvt Ltd Hyderabad SE 2B" I want to separate last words "SE 2B" and put in a separate column. Then Separate "Hyderabad" and put in another coulumn. The fields are Industry name, City and Rating (SE 2B here).

Kindly help.

Regards,
Adi

[vlady]

Assuming the words is in Cell A1.


=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1)," ",REPT(" ",30)),80),30))

and

=MID(A1,FIND("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,255)

***
try also this

=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A1)," ",REPT(" ",60)),120),60)) -> SE only
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))) -> 2B only

[nested.if1@gmail.com]

Hi...This may be helpful
1) Last word =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))
2) 2nd last word =TRIM(LEFT(RIGHT(SUBSTITUTE(" "&TRIM(A1)," ",REPT(" ",50)),100),50))
3) 3rd last =TRIM(LEFT(RIGHT(SUBSTITUTE(" "&TRIM(A1)," ",REPT(" ",50)),150),50))

[n.aditya.k]

Thanks a lot.

This was really useful. Now If I need to have the Industry name also in the column.

It will read "Tanmayi Ispat Pvt Ltd" "Hyderabad" "SE 2B" in 3 columns.

Now how to remove the city and Rating portion and get the industry name.

Note : All industry name have different string length.

e.g. The other ones "Technoweld Hyderabad SE 3B" and "Telecanor Global Ltd Hyderabad SME 1"

Regards,
Adi

[vlady]

try

=TRIM(LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-2))))