how do I move data to a new line in a cell after a comma?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to move data from one line in a cell to 2 lines in a cell. To
move all the data after a "comma"
Vis:
From
12 Beach St, Palm Beach Qld 4111
To
12 Beach St,
Palm Beach Qld 4111
Can anyone suggest a formula ?
Thanking you in advance
Jeff
 
1st part: =LEFT(A1,SEARCH(",",A1))
2nd part: =RIGHT(A1,LEN(A1)-SEARCH(",",A1)-1)

Regards,
Stefi

„Jeffro†ezt írta:
 
You want a line feed in the cell?

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

Entered in B1 with B1 set to wrap text and autofit.


Gord Dibben MS Excel MVP
 
Stefi
How would I write the formula for encompass a sting of cells vis: A2 - A22
say?
Thankyou again in advance
Jeff
 
Neither of those formulas will achieve your stated aim which was............
I am trying to move data from one line in a cell to 2 lines in a cell.
Click to expand...


Gord Dibben MS Excel MVP
 
Hi Gordon
Tried your respomse and it works well, can you advise me how to expand that
formula to do a column of addresses Vis: say from D1 to D30 ?
Thanking you again
jeff
 
Correction.........

=SUBSTITUTE(D1,", ",","&CHAR(10))

Enter the formula in E1 and drag/copy down to E30 or double-click on the fill
handle of E1.

Fill handle is the small black cross you see when you hover the cursor over the
bottom right corner of E1


Gord
 
Sorry, I was inadvertent and didn't notice the end of the sentence: "... in a
cell."
Stefi


„Gord Dibben†ezt írta:
 
Back
Top