| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove data that preceeds a character
I have a a column of data that has 4 seperate hyphens in the text.
EX. abc-123-abc-123-BEGIN NEEDED DATA AND THIS DATA ALSO HAS HYPHENS I WANT TO KEEP. How can I remove all the data that preceeds the 4th and final hyphen and keep the needed data. The # of characters in the preceeding data varies, so the 4th hyphen ( from the left) is the only consistent measure I have. -- Thanks, Ty |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove data that preceeds a character
Try,
=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))) Mike "Ty" wrote: I have a a column of data that has 4 seperate hyphens in the text. EX. abc-123-abc-123-BEGIN NEEDED DATA AND THIS DATA ALSO HAS HYPHENS I WANT TO KEEP. How can I remove all the data that preceeds the 4th and final hyphen and keep the needed data. The # of characters in the preceeding data varies, so the 4th hyphen ( from the left) is the only consistent measure I have. -- Thanks, Ty |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove data that preceeds a character
Try
=RIGHT(A2,LEN(A2)-FIND("^",SUBSTITUTE(A2,"-","^",4))) assuming your text does not contain the character "^". If it does then you will have to replace it with a character which will not be there... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Ty" wrote: I have a a column of data that has 4 seperate hyphens in the text. EX. abc-123-abc-123-BEGIN NEEDED DATA AND THIS DATA ALSO HAS HYPHENS I WANT TO KEEP. How can I remove all the data that preceeds the 4th and final hyphen and keep the needed data. The # of characters in the preceeding data varies, so the 4th hyphen ( from the left) is the only consistent measure I have. -- Thanks, Ty |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove data that preceeds a character
Mike,
Almost. This field has 4 hyphens in the beginning of the cell, followed by the data I need and then followed by data with more hyphens that I also need. This formula keeps only the data after the last hyphens. Here's another example, maybe I didnt explain it well. (the items i want removed are the abc-123's abc-123-abc-123-KEEP THIS DATA AND KEEP THIS DATA-CBA-321-CBA-321-CBA-321-KEEP THIS DATA AS WELL -- Thanks, Ty "Mike H" wrote: Try, =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))) Mike "Ty" wrote: I have a a column of data that has 4 seperate hyphens in the text. EX. abc-123-abc-123-BEGIN NEEDED DATA AND THIS DATA ALSO HAS HYPHENS I WANT TO KEEP. How can I remove all the data that preceeds the 4th and final hyphen and keep the needed data. The # of characters in the preceeding data varies, so the 4th hyphen ( from the left) is the only consistent measure I have. -- Thanks, Ty |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove data that preceeds a character
Sheeloo, Nice work, that did it..
Ty -- Thanks, Ty "Sheeloo" wrote: Try =RIGHT(A2,LEN(A2)-FIND("^",SUBSTITUTE(A2,"-","^",4))) assuming your text does not contain the character "^". If it does then you will have to replace it with a character which will not be there... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Ty" wrote: I have a a column of data that has 4 seperate hyphens in the text. EX. abc-123-abc-123-BEGIN NEEDED DATA AND THIS DATA ALSO HAS HYPHENS I WANT TO KEEP. How can I remove all the data that preceeds the 4th and final hyphen and keep the needed data. The # of characters in the preceeding data varies, so the 4th hyphen ( from the left) is the only consistent measure I have. -- Thanks, Ty |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove data that preceeds a character
How can I remove all the data that preceeds the 4th and final hyphen You explained yourself very well unfortunately the explanation had little to do with what you actually wanted. Mike "Ty" wrote: Mike, Almost. This field has 4 hyphens in the beginning of the cell, followed by the data I need and then followed by data with more hyphens that I also need. This formula keeps only the data after the last hyphens. Here's another example, maybe I didnt explain it well. (the items i want removed are the abc-123's abc-123-abc-123-KEEP THIS DATA AND KEEP THIS DATA-CBA-321-CBA-321-CBA-321-KEEP THIS DATA AS WELL -- Thanks, Ty "Mike H" wrote: Try, =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))) Mike "Ty" wrote: I have a a column of data that has 4 seperate hyphens in the text. EX. abc-123-abc-123-BEGIN NEEDED DATA AND THIS DATA ALSO HAS HYPHENS I WANT TO KEEP. How can I remove all the data that preceeds the 4th and final hyphen and keep the needed data. The # of characters in the preceeding data varies, so the 4th hyphen ( from the left) is the only consistent measure I have. -- Thanks, Ty |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove data that preceeds a character
Im not very good at this. Thanks again for your help, I really needed it
-- Thanks, Ty "Mike H" wrote: How can I remove all the data that preceeds the 4th and final hyphen You explained yourself very well unfortunately the explanation had little to do with what you actually wanted. Mike "Ty" wrote: Mike, Almost. This field has 4 hyphens in the beginning of the cell, followed by the data I need and then followed by data with more hyphens that I also need. This formula keeps only the data after the last hyphens. Here's another example, maybe I didnt explain it well. (the items i want removed are the abc-123's abc-123-abc-123-KEEP THIS DATA AND KEEP THIS DATA-CBA-321-CBA-321-CBA-321-KEEP THIS DATA AS WELL -- Thanks, Ty "Mike H" wrote: Try, =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"-","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))) Mike "Ty" wrote: I have a a column of data that has 4 seperate hyphens in the text. EX. abc-123-abc-123-BEGIN NEEDED DATA AND THIS DATA ALSO HAS HYPHENS I WANT TO KEEP. How can I remove all the data that preceeds the 4th and final hyphen and keep the needed data. The # of characters in the preceeding data varies, so the 4th hyphen ( from the left) is the only consistent measure I have. -- Thanks, Ty |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to remove character format under .csv? | Excel Discussion (Misc queries) | |||
| Remove ' character from copied excel cell to match data | Excel Discussion (Misc queries) | |||
| Remove character from imported data | Excel Discussion (Misc queries) | |||
| How to remove a character from the first index? | Excel Discussion (Misc queries) | |||
| Remove a particular character using formulas | Excel Discussion (Misc queries) | |||
Linear Mode
