If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Text to column where to put result
I am using text to column.
Have name-name2 They are separated by a "dash". Want them in two separate columns. What do I put in the destination cell? |
#2
|
|||
|
|||
Text to column where to put result
Assume that your data is in a1 cell like the below.
A1 cell name-name2 Copy and paste the below formula in B1 cell =IF(A1="","",TRIM(LEFT(A1,FIND("-",A1)-1))) Copy and paste the below formula in C1 cell =IF(A1="","",TRIM(MID(A1,FIND("-",A1)+1,255))) -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Going Crazy with excel" wrote: I am using text to column. Have name-name2 They are separated by a "dash". Want them in two separate columns. What do I put in the destination cell? |
#3
|
|||
|
|||
Text to column where to put result
Hi
Assuming your data is starting at A1, if you select B1, it will be transferred to column B and C. If you leave it at A1 then it will be in column A and B. If you don't like the result, you can undo and start over. HTH John "Going Crazy with excel" wrote in message ... I am using text to column. Have name-name2 They are separated by a "dash". Want them in two separate columns. What do I put in the destination cell? |
#4
|
|||
|
|||
Text to column where to put result
I think you are on the right track MS-Exl-Learner, but it didn't work.
Got a "value" error. Originally tried the text to column tab, but that would not recognize the "dash" -, instead of the space. Yup that would be too easy. Guess the formula, is the only way to go. Just need to tweek it. Any ideas? Again, my cell looks like this: ralph-cramdon "Ms-Exl-Learner" wrote: Assume that your data is in a1 cell like the below. A1 cell name-name2 Copy and paste the below formula in B1 cell =IF(A1="","",TRIM(LEFT(A1,FIND("-",A1)-1))) Copy and paste the below formula in C1 cell =IF(A1="","",TRIM(MID(A1,FIND("-",A1)+1,255))) -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Going Crazy with excel" wrote: I am using text to column. Have name-name2 They are separated by a "dash". Want them in two separate columns. What do I put in the destination cell? |
#5
|
|||
|
|||
Text to column where to put result
Look at the TextToColumns function again.
In Delemiter section check 'Other' and enter the dash. Follow the guide, and see what happens. Regards, Per On 22 Maj, 01:37, Going Crazy with excel wrote: I think you are on the right track MS-Exl-Learner, but it didn't work. Got a "value" error. Originally tried the text to column tab, but that would not recognize the "dash" -, instead of the space. *Yup that would be too easy. Guess the formula, is the only way to go. *Just need to tweek it. *Any ideas? Again, my cell looks like this: ralph-cramdon "Ms-Exl-Learner" wrote: Assume that your data is in a1 cell like the below. A1 cell name-name2 Copy and paste the below formula in B1 cell =IF(A1="","",TRIM(LEFT(A1,FIND("-",A1)-1))) Copy and paste the below formula in C1 cell =IF(A1="","",TRIM(MID(A1,FIND("-",A1)+1,255))) -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Going Crazy with excel" wrote: I am using text to column. Have name-name2 They are separated by a "dash". *Want them in two separate columns. What do I put in the destination cell?- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#6
|
|||
|
|||
Text to column where to put result
Per Jessen, I did try that. Will not separte one name from the other for
whatever reason. Of course, that would be to simple. That's the reason I am contemplating going back with a formula, just can't find the right one. "Per Jessen" wrote: Look at the TextToColumns function again. In Delemiter section check 'Other' and enter the dash. Follow the guide, and see what happens. Regards, Per On 22 Maj, 01:37, Going Crazy with excel wrote: I think you are on the right track MS-Exl-Learner, but it didn't work. Got a "value" error. Originally tried the text to column tab, but that would not recognize the "dash" -, instead of the space. Yup that would be too easy. Guess the formula, is the only way to go. Just need to tweek it. Any ideas? Again, my cell looks like this: ralph-cramdon "Ms-Exl-Learner" wrote: Assume that your data is in a1 cell like the below. A1 cell name-name2 Copy and paste the below formula in B1 cell =IF(A1="","",TRIM(LEFT(A1,FIND("-",A1)-1))) Copy and paste the below formula in C1 cell =IF(A1="","",TRIM(MID(A1,FIND("-",A1)+1,255))) -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Going Crazy with excel" wrote: I am using text to column. Have name-name2 They are separated by a "dash". Want them in two separate columns. What do I put in the destination cell?- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - . |
#7
|
|||
|
|||
Text to column where to put result
Perhaps you've got the wrong sort of dash character. Copy from your data to
your formula or to your Data/ Text to Columns dialogue. -- David Biddulph "Going Crazy with excel" wrote in message ... Per Jessen, I did try that. Will not separte one name from the other for whatever reason. Of course, that would be to simple. That's the reason I am contemplating going back with a formula, just can't find the right one. "Per Jessen" wrote: Look at the TextToColumns function again. In Delemiter section check 'Other' and enter the dash. Follow the guide, and see what happens. Regards, Per On 22 Maj, 01:37, Going Crazy with excel wrote: I think you are on the right track MS-Exl-Learner, but it didn't work. Got a "value" error. Originally tried the text to column tab, but that would not recognize the "dash" -, instead of the space. Yup that would be too easy. Guess the formula, is the only way to go. Just need to tweek it. Any ideas? Again, my cell looks like this: ralph-cramdon "Ms-Exl-Learner" wrote: Assume that your data is in a1 cell like the below. A1 cell name-name2 Copy and paste the below formula in B1 cell =IF(A1="","",TRIM(LEFT(A1,FIND("-",A1)-1))) Copy and paste the below formula in C1 cell =IF(A1="","",TRIM(MID(A1,FIND("-",A1)+1,255))) -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Going Crazy with excel" wrote: I am using text to column. Have name-name2 They are separated by a "dash". Want them in two separate columns. What do I put in the destination cell?- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - . |
Thread Tools | |
Display Modes | |
|
|