|
how do I move data to a new line in a cell after a comma?
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 |
how do I move data to a new line in a cell after a comma?
1st part: =LEFT(A1,SEARCH(",",A1)) 2nd part: =RIGHT(A1,LEN(A1)-SEARCH(",",A1)-1) Regards, Stefi €˛Jeffro€¯ ezt Ć*rta: 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 |
how do I move data to a new line in a cell after a comma?
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 On Wed, 3 Oct 2007 21:50:02 -0700, Jeffro wrote: 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 |
how do I move data to a new line in a cell after a comma?
Stefi
How would I write the formula for encompass a sting of cells vis: A2 - A22 say? Thankyou again in advance Jeff "Stefi" wrote: 1st part: =LEFT(A1,SEARCH(",",A1)) 2nd part: =RIGHT(A1,LEN(A1)-SEARCH(",",A1)-1) Regards, Stefi €˛Jeffro€¯ ezt Ć*rta: 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 |
how do I move data to a new line in a cell after a comma?
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. Gord Dibben MS Excel MVP On Thu, 4 Oct 2007 16:23:01 -0700, Jeffro wrote: Stefi How would I write the formula for encompass a sting of cells vis: A2 - A22 say? Thankyou again in advance Jeff "Stefi" wrote: 1st part: =LEFT(A1,SEARCH(",",A1)) 2nd part: =RIGHT(A1,LEN(A1)-SEARCH(",",A1)-1) Regards, Stefi „Jeffro” ezt ķrta: 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 |
how do I move data to a new line in a cell after a comma?
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 "Gord Dibben" wrote: 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 On Wed, 3 Oct 2007 21:50:02 -0700, Jeffro wrote: 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 |
how do I move data to a new line in a cell after a comma?
Change A1 to D1 then drag/copy the formula down to D30
Gord On Thu, 4 Oct 2007 18:53:00 -0700, Jeffro wrote: 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 "Gord Dibben" wrote: 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 On Wed, 3 Oct 2007 21:50:02 -0700, Jeffro wrote: 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 |
how do I move data to a new line in a cell after a comma?
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 On Thu, 04 Oct 2007 20:48:26 -0700, Gord Dibben <gorddibbATshawDOTca wrote: Change A1 to D1 then drag/copy the formula down to D30 Gord On Thu, 4 Oct 2007 18:53:00 -0700, Jeffro wrote: 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 "Gord Dibben" wrote: 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 On Wed, 3 Oct 2007 21:50:02 -0700, Jeffro wrote: 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 |
how do I move data to a new line in a cell after a comma?
Sorry, I was inadvertent and didn't notice the end of the sentence: "... in a
cell." Stefi €˛Gord Dibben€¯ ezt Ć*rta: 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. Gord Dibben MS Excel MVP On Thu, 4 Oct 2007 16:23:01 -0700, Jeffro wrote: Stefi How would I write the formula for encompass a sting of cells vis: A2 - A22 say? Thankyou again in advance Jeff "Stefi" wrote: 1st part: =LEFT(A1,SEARCH(",",A1)) 2nd part: =RIGHT(A1,LEN(A1)-SEARCH(",",A1)-1) Regards, Stefi €˛Jeffro€¯ ezt Ć*rta: 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 |
how do I move data to a new line in a cell after a comma?
Thankyou very much, it now seems very easy once you explained .
Ciao jeff "Gord Dibben" wrote: 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 On Thu, 04 Oct 2007 20:48:26 -0700, Gord Dibben <gorddibbATshawDOTca wrote: Change A1 to D1 then drag/copy the formula down to D30 Gord On Thu, 4 Oct 2007 18:53:00 -0700, Jeffro wrote: 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 "Gord Dibben" wrote: 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 On Wed, 3 Oct 2007 21:50:02 -0700, Jeffro wrote: 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 |
| All times are GMT +1. The time now is 04:28 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com