• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

To find out Company name and Only City from the attached data.

R

Ramesh Deo

Member
Hello SM Sr,

I got back with simple query,please solve this attached sample file.

Thanks &Regards
Ramesh Deo
 

Attachments

  • data.xlsx
    13.1 KB · Views: 11
That was a very brief description, but I think this is what you want.
Formula for company name:
=TRIM(MID(SUBSTITUTE($A1," - ",REPT(" ",999)),999*2,999))

Formula for address (very similar):
=TRIM(MID(SUBSTITUTE($A1," - ",REPT(" ",999)),999*3,999))
 
Luke M said:
That was a very brief description, but I think this is what you want.
Formula for company name:
=TRIM(MID(SUBSTITUTE($A1," - ",REPT(" ",999)),999*2,999))

Formula for address (very similar):
=TRIM(MID(SUBSTITUTE($A1," - ",REPT(" ",999)),999*3,999))
Click to expand...
Hi Luke Sir,

will u let me know please b'coz i din't get abot REPT(" ",999)),999*2,999)) for both.
 
I'm not sure what you are asking...you want to know how the formula works?
Formula replaces all the " - " dashes with a giant space. The company name is in the 3rd "column" of data, so we go to the 2nd large space, grab a large section (going from that large space to next large space), and then TRIM the data back down to size. The city name is in next column, so we increase the *2 to be *3.
 
Luke M said:
I'm not sure what you are asking...you want to know how the formula works?
Formula replaces all the " - " dashes with a giant space. The company name is in the 3rd "column" of data, so we go to the 2nd large space, grab a large section (going from that large space to next large space), and then TRIM the data back down to size. The city name is in next column, so we increase the *2 to be *3.
Click to expand...

ok thnks for the explanation,bt wat will we do if only spaces available instead of dashes("-") in data??? can we use same formula.???
 
No, you would need to use whatever the unique identifier is between the columns. If it's 4 spaces, replace 4 spaces. If it's a pipe symbol, replace the pipe symbol. If it's a semicolon, replace the semicolon. If it's only single spaces, then you no longer have a unique identifier, and your sample data was not a good representation of your actual data.
 
Luke M said:
No, you would need to use whatever the unique identifier is between the columns. If it's 4 spaces, replace 4 spaces. If it's a pipe symbol, replace the pipe symbol. If it's a semicolon, replace the semicolon. If it's only single spaces, then you no longer have a unique identifier, and your sample data was not a good representation of your actual data.
Click to expand...

Hi Luke sir,
Thanks for the explanation!!
actually dis was not mine sample data,sm1 asked to solve it n i tried to solve through mid formula bt i only got company name not city name.then i need help,bt plz tell me one thing if we hav only single spaces in sample data then plz make me understand,hw can we do this????
 
If you only have single spaces, no formula would be able to help, as there's no way for the computer to know when one item ends and another begins. You would need either a fixed length, or unique delimiter. Those are the only two options.
 
Luke M said:
If you only have single spaces, no formula would be able to help, as there's no way for the computer to know when one item ends and another begins. You would need either a fixed length, or unique delimiter. Those are the only two options.
Click to expand...

thanks 4 making me understand about such types of issues!!!!
 
You must log in or register to reply here.
Back
Top