CODEWITHSUNDEEP
excelexcel-formulaexcel-2013
SmallFries BigGuys
SmallFries BigGuys

Reputation: 171

Parse Data From Excel Cell

In Excel 2013 in one cell (but on different lines) there is
Store Name
Address
City, State, Zip

I want to parse it out so that I have Name, Address, City, State, Zip all in one cell. This is what my data looks like in cell A1

Bell & Jean Industries
6413 Oakhaven Ridge Road
Foxfire, FL  55121

Now I tried to get crafty and input in cell E1 =A1 which puts the data on one line, BUT it does not add a space between Company, Address and City so my concatenation will not work (also, if I right click the cell and uncheck wrap text it is the same result).

I normally will use this syntax to strip out the needed data, but since there is no space, my syntax does not work. How can I strip out Address, City, State, Zip info when the data is presented in this format?

Upvotes: 2

Views: 133

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

=SUBSTITUTE(A1,CHAR(10)," ")

You need to replace the new line character with a space

Upvotes: 2

Related Questions