Pulling Multiple Text Strings in a One Cell

  • Thread starter Thread starter RebLan
  • Start date Start date
R

RebLan

Okay....this is what i need help on. These multiple lines in one cell is
throwing me off. If i make the multiple lines appear as a single text string
in one cell, i still have spacing issues.

(A1)

ATM ID: Example
Project ID: 12345
Project Name: HELP
Site Name: Company Name
Model: 2345e EF
SN: Not Assigned

I need it to look like this all in one cell, due to a system upload.

ATM ID: Example**Project ID: 12345**Project Name: HELP**Site Name: Company
Name**Model: 2150e EF**SN: Not Assigned

Please HELP!!!

Thanks
 
Try:
=LEFT(A1,15)&"* *"&MID(A1,16,17)&"* *"&MID(A1,34,19)&"* *"&MID(A1,53,25)&"*
*"&MID(A1,85,18)&"* *"&RIGHT(A1,16)
 
Select the range to fix (at least two cells)
Edit|Replace (in xl2003 menus)
what: ctrl-j (hold the control key while you hit the j key)
(It may not look like anything was entered, but trust it!)
with: **
Replace all
(Make sure that the options are set correctly, too.)

And then widen the column to see if it worked like you want.
 
This formula works for the most part for my example, but keep in mind the
information will change. The info that will stay constant is ATM ID: Project
Name: , Site Name: ,Model:, SN:. However, the rest of the info will
change...so I don't think I can use this type of formula that counts number
of characters. Is there a way to FIND the text which will be constant such as
PROJECT ID: and add ** to the left of the word PROJECT? Then add on to the
formula to find Project Name: , Site Name: ,Model:,SN: and add ** to the left
of those words?
 
Dave,
This was an easy fix. Thanks so much!

Dave Peterson said:
Select the range to fix (at least two cells)
Edit|Replace (in xl2003 menus)
what: ctrl-j (hold the control key while you hit the j key)
(It may not look like anything was entered, but trust it!)
with: **
Replace all
(Make sure that the options are set correctly, too.)

And then widen the column to see if it worked like you want.
Click to expand...
 
Back
Top