| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formula
Hi All,
I wish to create a formula to be able to split a text string in a given place and copy one part to another cell and the other part to a different cell. Please let me explain.... I have a worksheet ranging from cell A1 to A6000 containing a list of all my MP3 files stored on my PC. The format of all the file names is for example Artist Name - Song Title eg: Madonna - Hung Up. I wist to take all characters before the space hyphen space and copy this to column B and all charachters after the space hyphen space and copy these to column C. This is what I should end up with: A1 B1 C1 Madonna - Hung Up Madonna Hung Up The text strings in cell A1 : A6000 are all varying lenghts and this is where I a struggeling to get a formula to work. I am using Excel version 2002 SP3 Many thanks Dave |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
You will need to combine a couple of functions to get the answers you desire.
use this formula for cell B2 =LEFT(A2, (FIND("-",A2))-1) and use this formula for cell C2 =RIGHT(A2, (FIND("-",A2))-1) HTH and GL "CBR1100XX Blackbird" wrote: Hi All, I wish to create a formula to be able to split a text string in a given place and copy one part to another cell and the other part to a different cell. Please let me explain.... I have a worksheet ranging from cell A1 to A6000 containing a list of all my MP3 files stored on my PC. The format of all the file names is for example Artist Name - Song Title eg: Madonna - Hung Up. I wist to take all characters before the space hyphen space and copy this to column B and all charachters after the space hyphen space and copy these to column C. This is what I should end up with: A1 B1 C1 Madonna - Hung Up Madonna Hung Up The text strings in cell A1 : A6000 are all varying lenghts and this is where I a struggeling to get a formula to work. I am using Excel version 2002 SP3 Many thanks Dave |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
Hi JR,
many thanks for the quick response. The formula works great on the first cell but when I drag it to the celkls below it misses out the first wotd of the song title as below: Madonna - Hung Up Madonna Hung Up Neil Diamond - Love On The Rocks Neil Diamond On The Rocks Aqua - Barbie Girl Aqua Girl Any thought why it should do this ? Thanks "JR Hester" wrote: You will need to combine a couple of functions to get the answers you desire. use this formula for cell B2 =LEFT(A2, (FIND("-",A2))-1) and use this formula for cell C2 =RIGHT(A2, (FIND("-",A2))-1) HTH and GL "CBR1100XX Blackbird" wrote: Hi All, I wish to create a formula to be able to split a text string in a given place and copy one part to another cell and the other part to a different cell. Please let me explain.... I have a worksheet ranging from cell A1 to A6000 containing a list of all my MP3 files stored on my PC. The format of all the file names is for example Artist Name - Song Title eg: Madonna - Hung Up. I wist to take all characters before the space hyphen space and copy this to column B and all charachters after the space hyphen space and copy these to column C. This is what I should end up with: A1 B1 C1 Madonna - Hung Up Madonna Hung Up The text strings in cell A1 : A6000 are all varying lenghts and this is where I a struggeling to get a formula to work. I am using Excel version 2002 SP3 Many thanks Dave |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
Dear "CBR1100XX Blackbird"
In B1 try =TRIM(LEFT(A1,FIND("-",A1)-1)) and in C1 try =TRIM(MID(A1,FIND("-",A1)+1,LEN(A1))) Copy that down..and enjoy If this post helps click Yes --------------- Jacob Skaria "CBR1100XX Blackbird" wrote: Hi JR, many thanks for the quick response. The formula works great on the first cell but when I drag it to the celkls below it misses out the first wotd of the song title as below: Madonna - Hung Up Madonna Hung Up Neil Diamond - Love On The Rocks Neil Diamond On The Rocks Aqua - Barbie Girl Aqua Girl Any thought why it should do this ? Thanks "JR Hester" wrote: You will need to combine a couple of functions to get the answers you desire. use this formula for cell B2 =LEFT(A2, (FIND("-",A2))-1) and use this formula for cell C2 =RIGHT(A2, (FIND("-",A2))-1) HTH and GL "CBR1100XX Blackbird" wrote: Hi All, I wish to create a formula to be able to split a text string in a given place and copy one part to another cell and the other part to a different cell. Please let me explain.... I have a worksheet ranging from cell A1 to A6000 containing a list of all my MP3 files stored on my PC. The format of all the file names is for example Artist Name - Song Title eg: Madonna - Hung Up. I wist to take all characters before the space hyphen space and copy this to column B and all charachters after the space hyphen space and copy these to column C. This is what I should end up with: A1 B1 C1 Madonna - Hung Up Madonna Hung Up The text strings in cell A1 : A6000 are all varying lenghts and this is where I a struggeling to get a formula to work. I am using Excel version 2002 SP3 Many thanks Dave |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
Jacob,
Thanks, This works a treat !! "Jacob Skaria" wrote: Dear "CBR1100XX Blackbird" In B1 try =TRIM(LEFT(A1,FIND("-",A1)-1)) and in C1 try =TRIM(MID(A1,FIND("-",A1)+1,LEN(A1))) Copy that down..and enjoy If this post helps click Yes --------------- Jacob Skaria "CBR1100XX Blackbird" wrote: Hi JR, many thanks for the quick response. The formula works great on the first cell but when I drag it to the celkls below it misses out the first wotd of the song title as below: Madonna - Hung Up Madonna Hung Up Neil Diamond - Love On The Rocks Neil Diamond On The Rocks Aqua - Barbie Girl Aqua Girl Any thought why it should do this ? Thanks "JR Hester" wrote: You will need to combine a couple of functions to get the answers you desire. use this formula for cell B2 =LEFT(A2, (FIND("-",A2))-1) and use this formula for cell C2 =RIGHT(A2, (FIND("-",A2))-1) HTH and GL "CBR1100XX Blackbird" wrote: Hi All, I wish to create a formula to be able to split a text string in a given place and copy one part to another cell and the other part to a different cell. Please let me explain.... I have a worksheet ranging from cell A1 to A6000 containing a list of all my MP3 files stored on my PC. The format of all the file names is for example Artist Name - Song Title eg: Madonna - Hung Up. I wist to take all characters before the space hyphen space and copy this to column B and all charachters after the space hyphen space and copy these to column C. This is what I should end up with: A1 B1 C1 Madonna - Hung Up Madonna Hung Up The text strings in cell A1 : A6000 are all varying lenghts and this is where I a struggeling to get a formula to work. I am using Excel version 2002 SP3 Many thanks Dave |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help with formula
Cheers!
If this post helps click Yes --------------- Jacob Skaria "CBR1100XX Blackbird" wrote: Jacob, Thanks, This works a treat !! "Jacob Skaria" wrote: Dear "CBR1100XX Blackbird" In B1 try =TRIM(LEFT(A1,FIND("-",A1)-1)) and in C1 try =TRIM(MID(A1,FIND("-",A1)+1,LEN(A1))) Copy that down..and enjoy If this post helps click Yes --------------- Jacob Skaria "CBR1100XX Blackbird" wrote: Hi JR, many thanks for the quick response. The formula works great on the first cell but when I drag it to the celkls below it misses out the first wotd of the song title as below: Madonna - Hung Up Madonna Hung Up Neil Diamond - Love On The Rocks Neil Diamond On The Rocks Aqua - Barbie Girl Aqua Girl Any thought why it should do this ? Thanks "JR Hester" wrote: You will need to combine a couple of functions to get the answers you desire. use this formula for cell B2 =LEFT(A2, (FIND("-",A2))-1) and use this formula for cell C2 =RIGHT(A2, (FIND("-",A2))-1) HTH and GL "CBR1100XX Blackbird" wrote: Hi All, I wish to create a formula to be able to split a text string in a given place and copy one part to another cell and the other part to a different cell. Please let me explain.... I have a worksheet ranging from cell A1 to A6000 containing a list of all my MP3 files stored on my PC. The format of all the file names is for example Artist Name - Song Title eg: Madonna - Hung Up. I wist to take all characters before the space hyphen space and copy this to column B and all charachters after the space hyphen space and copy these to column C. This is what I should end up with: A1 B1 C1 Madonna - Hung Up Madonna Hung Up The text strings in cell A1 : A6000 are all varying lenghts and this is where I a struggeling to get a formula to work. I am using Excel version 2002 SP3 Many thanks Dave |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
