Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split Text
I am trying to split data from one cell to another, I tried using the TEXT To
COLUMNS wizard but it wants to split the cell as a formula instead of the value. A1 =Sheet1!A1 A1 displays the value (please,help) How can I split the value of A1 into A2 and A3? Any help is greatly appriciated. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split Text
Try these text formulas:
In B1, =LEFT(A1,FIND(",",A1)-1) In C1, =RIGHT(A1,LEN(A1)-FIND(",",A1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Gabe" wrote in message ... I am trying to split data from one cell to another, I tried using the TEXT To COLUMNS wizard but it wants to split the cell as a formula instead of the value. A1 =Sheet1!A1 A1 displays the value (please,help) How can I split the value of A1 into A2 and A3? Any help is greatly appriciated. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split Text
Change column A to values first
Select column A, then Copy EditPastespecialValues -- HTH RP (remove nothere from the email address if mailing direct) "Gabe" wrote in message ... I am trying to split data from one cell to another, I tried using the TEXT To COLUMNS wizard but it wants to split the cell as a formula instead of the value. A1 =Sheet1!A1 A1 displays the value (please,help) How can I split the value of A1 into A2 and A3? Any help is greatly appriciated. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split Text
That worked pretty good, but now my format is messed up? I'm looking to
prefix the result with an "E" how would I do that? Before I just used a custom format of "E"#### that worked pretty good but when I use the formula it doesn't work. How can prefix it with an "E" for the following? A1 =Sheet1!A1 A1 displays the value (GE00205189) B1 =RIGHT(A1,4) B1 displays the value (5189) Thanks. "RagDyer" wrote: Try these text formulas: In B1, =LEFT(A1,FIND(",",A1)-1) In C1, =RIGHT(A1,LEN(A1)-FIND(",",A1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Gabe" wrote in message ... I am trying to split data from one cell to another, I tried using the TEXT To COLUMNS wizard but it wants to split the cell as a formula instead of the value. A1 =Sheet1!A1 A1 displays the value (please,help) How can I split the value of A1 into A2 and A3? Any help is greatly appriciated. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split Text
="E"&LEFT(A1,FIND(",",A1)-1)
="E"&RIGHT(A1,LEN(A1)-FIND(",",A1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Gabe" wrote in message ... That worked pretty good, but now my format is messed up? I'm looking to prefix the result with an "E" how would I do that? Before I just used a custom format of "E"#### that worked pretty good but when I use the formula it doesn't work. How can prefix it with an "E" for the following? A1 =Sheet1!A1 A1 displays the value (GE00205189) B1 =RIGHT(A1,4) B1 displays the value (5189) Thanks. "RagDyer" wrote: Try these text formulas: In B1, =LEFT(A1,FIND(",",A1)-1) In C1, =RIGHT(A1,LEN(A1)-FIND(",",A1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Gabe" wrote in message ... I am trying to split data from one cell to another, I tried using the TEXT To COLUMNS wizard but it wants to split the cell as a formula instead of the value. A1 =Sheet1!A1 A1 displays the value (please,help) How can I split the value of A1 into A2 and A3? Any help is greatly appriciated. Thanks. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split Text
="E"&RIGHT(A1,4) Regards JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=498065 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split Text
The cell currently outputs a text string that looks like a number, so it is
formatted as text (a format like "E"#### applys only to numbers). You have three options. I. Change the formula to output a number. =VALUE(RIGHT(A1,4)) and use the same custom number format of "E"#### II. Leave the formula alone =RIGHT(A1,4) and change the format to something like #;-#;0;"E"@ II. Change the formula to output a text string that includes "E". ="E"&RIGHT(A1,4) and format as general "Gabe" wrote: That worked pretty good, but now my format is messed up? I'm looking to prefix the result with an "E" how would I do that? Before I just used a custom format of "E"#### that worked pretty good but when I use the formula it doesn't work. How can prefix it with an "E" for the following? A1 =Sheet1!A1 A1 displays the value (GE00205189) B1 =RIGHT(A1,4) B1 displays the value (5189) Thanks. "RagDyer" wrote: Try these text formulas: In B1, =LEFT(A1,FIND(",",A1)-1) In C1, =RIGHT(A1,LEN(A1)-FIND(",",A1)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Gabe" wrote in message ... I am trying to split data from one cell to another, I tried using the TEXT To COLUMNS wizard but it wants to split the cell as a formula instead of the value. A1 =Sheet1!A1 A1 displays the value (please,help) How can I split the value of A1 into A2 and A3? Any help is greatly appriciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Linked cells and text boxes | Excel Discussion (Misc queries) | |||
dates and text | Excel Discussion (Misc queries) | |||
Cell text based on 4 condition test | Excel Worksheet Functions | |||
SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |