Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text after a dash
I have a list of songs, but the name of the song and artist are all in the
same cell. I'd like to be able to sort by artist. Is there a formula that I could enter in B1 ,B2 , etc. that could text everything starting with the 3rd character to the right of each dash (-) ? (A1) Earth Angel - The Penguins (A2) El Paso - Marty Robbins so that the B column result would be The Penguins Marty Robbins Note: The 13 characters in these samples are obviously just a coincidence, and other cells will have different number of characters. Thanks, Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text after a dash
Try this:
For text in A1 B1: =TRIM(RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND("- ",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))) ))) That pulls the text to right of the last occurrence of the dash. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Steve" wrote: I have a list of songs, but the name of the song and artist are all in the same cell. I'd like to be able to sort by artist. Is there a formula that I could enter in B1 ,B2 , etc. that could text everything starting with the 3rd character to the right of each dash (-) ? (A1) Earth Angel - The Penguins (A2) El Paso - Marty Robbins so that the B column result would be The Penguins Marty Robbins Note: The 13 characters in these samples are obviously just a coincidence, and other cells will have different number of characters. Thanks, Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text after a dash
If you want the text after the first dash:
For text in A1 B1: =TRIM(RIGHT(A1,LEN(A1)-SEARCH("-",A1))) *********** Regards, Ron XL2002, WinXP-Pro "Steve" wrote: I have a list of songs, but the name of the song and artist are all in the same cell. I'd like to be able to sort by artist. Is there a formula that I could enter in B1 ,B2 , etc. that could text everything starting with the 3rd character to the right of each dash (-) ? (A1) Earth Angel - The Penguins (A2) El Paso - Marty Robbins so that the B column result would be The Penguins Marty Robbins Note: The 13 characters in these samples are obviously just a coincidence, and other cells will have different number of characters. Thanks, Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text after a dash
Perfect. Thanks much. This one actually worked a little better than the other
one, because if there was a dash in the song title, the result of the other one was not correct. This one seems to work completely. Thanks again, Steve "Ron Coderre" wrote: Try this: For text in A1 B1: =TRIM(RIGHT(A1,LEN(A1)-LOOKUP(LEN(A1),FIND("- ",A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1))) ))) That pulls the text to right of the last occurrence of the dash. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Steve" wrote: I have a list of songs, but the name of the song and artist are all in the same cell. I'd like to be able to sort by artist. Is there a formula that I could enter in B1 ,B2 , etc. that could text everything starting with the 3rd character to the right of each dash (-) ? (A1) Earth Angel - The Penguins (A2) El Paso - Marty Robbins so that the B column result would be The Penguins Marty Robbins Note: The 13 characters in these samples are obviously just a coincidence, and other cells will have different number of characters. Thanks, Steve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text after a dash
On Wed, 25 Jan 2006 18:11:02 -0800, "Steve"
wrote: I have a list of songs, but the name of the song and artist are all in the same cell. I'd like to be able to sort by artist. Is there a formula that I could enter in B1 ,B2 , etc. that could text everything starting with the 3rd character to the right of each dash (-) ? (A1) Earth Angel - The Penguins (A2) El Paso - Marty Robbins so that the B column result would be The Penguins Marty Robbins Note: The 13 characters in these samples are obviously just a coincidence, and other cells will have different number of characters. Thanks, Steve Note that if you start with the "3rd character to the right of each dash" your results would be: he Penguins arty Robbins So I changed it to the 2nd character: =MID(A1,FIND("-",A1)+2,255) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Concatenate function to generate text in Text Box | Charts and Charting in Excel | |||
merged cells into one text cell, size varies dependant on text dat | Excel Discussion (Misc queries) | |||
Need insert a dash in between last 4 digits in text string | Excel Worksheet Functions | |||
SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |