extraction

[jayalaxmi]

Hi there,

I am stucked up with some extraction & retreiving of data. For reference I am attaching a file. I just need for eg..Gandhinagar, Ahmedabad eg 2 Gandhinagar, Ahmedabad in the next column. pls any help is appreciated. thank u for reading this.

 

[bosco_yip]

Try,

B2, formula copy across and down :

=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,".",""),CHAR(160),""),",",REPT(" ",300)),(3-COLUMNS($A$1:A$1))*300),300))

Regards
Bosco

 

[jayalaxmi]

Hello

Thank you bosco for your instant reply . But the output i needed is Gandhinagar, Ahmedabad and so on. but yours is only showing gandhinagar

regards
jaya

 

[bosco_yip]

Hello

Thank you bosco for your instant reply . But the output i needed is Gandhinagar, Ahmedabad and so on. but yours is only showing gandhinagar

regards
jaya

Then, check this revised.

Regards

 

[azumi]

Another option, put this in C2 and copied down

=SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",60)),120))," ",", "),".","")

 

[bosco_yip]

Another option, put this in C2 and copied down

=SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",60)),120))," ",", "),".","")

The formula returned with additional space and "," which needed to remove,

for example :

1] In B7 return : India, Aluva --> additional space in front of "India" ( similar to cell B8, B9, B37…etc)

2] In B10 return : Kallai, Road, Calicut --> additional "," in front of "Road" ( similar to cell B11, B12, B13…etc)

Regards

 

[azumi]

@bosco_yip

you true, thanks for corrections

 

[jayalaxmi]

Hello

Thank you all for your kind help

Regards
Jaya