Extracting text

maxwave28

New Member
Joined
Dec 31, 2003
Messages
2
I have imported from the web a spread sheet in CSV (comma delimited) formating. However instead of having multiple columns for address 1, address 2, city, state and zip, there is only one column. The entire addres in is one cell for each entry. Therefore everything in quotes below is in one cell.

"1900 South State College Blvd
Suite 600
Anaheim, CA
USA, 92806"

Each line is seperated with an alt + enter.

What I want to do is apply a formula or something so that each line becomes its own cell on one row.

Eg.
Column C Column D
1900 South State College Blvd Suite 600

Please help thank you
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
You could use this formula...
Code:
=SUBSTITUTE(A1,CHAR(10),"|")
To convert your information to this format...
Code:
1900 South State College Blvd |Suite 600 |Anaheim, CA |USA, 92806
You would need to apply this formula to your list, and then you could use the Text to Columns wizard with a delimiter of "|" to separate it into individual cells.
 
Upvote 0
Thank you so much that helped tremendously

I have one more question what character # is the a comma because i am going to seperate the city and state that are listed as new york, ny and the zip codes that are listed usa, 10021 into their own cells

thank you very much
 
Upvote 0
In the Text to Columns wizard, you can tick several separators (for example | and ,) at the same time, so you can do this in one step, no need to replace the commas with anything else.
 
Upvote 0
I agree with herilane.. why we need to use formula subsititute.. Tommy gun .. is there any specific reason ?
 
Upvote 0
nisht, I wasn't disagreeing with TommyGun, just respondingto the post after that. I believe you'd need to replace the line break characters, because those are not recognised by the wizard. Commas are.
 
Upvote 0
nisht said:
I agree with herilane.. why we need to use formula subsititute.. Tommy gun .. is there any specific reason ?

Perferct answer here...
herilane said:
I believe you'd need to replace the line break characters, because those are not recognised by the wizard.
 
Upvote 0
maxwave28 said:
Thank you so much that helped tremendously

I have one more question what character # is the a comma because i am going to seperate the city and state that are listed as new york, ny and the zip codes that are listed usa, 10021 into their own cells

thank you very much

Formula:
=CODE(",")
Result:
44
 
Upvote 0
TommyGun said:
You could use this formula...
Code:
=SUBSTITUTE(A1,CHAR(10),"|")
To convert your information to this format...
Code:
1900 South State College Blvd |Suite 600 |Anaheim, CA |USA, 92806
You would need to apply this formula to your list, and then you could use the Text to Columns wizard with a delimiter of "|" to separate it into individual cells.
Hi Tommy:

There are two special characters involved --aren't there? -- CHAR(10), and CHAR(13), so would we not not need another substitutution to eliminate Char(13)?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top