| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to remove characters in a cell that precede a specific hyphen
I have a column of cells that have excess data that needs removed. The #of
characters vary. I'm needing a formula to delete all the characters that precede the 2nd hyphen as well as delete the 2nd hyphen. Ex. [ABC-12345-12345] I need the cell to read [12345] Can anyone provide a formula that can remove this data? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyphen
Presuming your data in A1 down looks like this:
ABC-12345-12344 ABC-12345-12355 etc In B1: =MID(A1,SEARCH("-",A1,SEARCH("-",A1)+1)+1,99)+0 Copy down to return the numbers after the 2nd hyphen as real numbers, viz: 12344 12355 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "brantty" wrote: I have a column of cells that have excess data that needs removed. The #of characters vary. I'm needing a formula to delete all the characters that precede the 2nd hyphen as well as delete the 2nd hyphen. Ex. [ABC-12345-12345] I need the cell to read [12345] Can anyone provide a formula that can remove this data? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyphen
hi,
you said the number of characters vary. could you supply more examples of the data? Regards FSt1 "brantty" wrote: I have a column of cells that have excess data that needs removed. The #of characters vary. I'm needing a formula to delete all the characters that precede the 2nd hyphen as well as delete the 2nd hyphen. Ex. [ABC-12345-12345] I need the cell to read [12345] Can anyone provide a formula that can remove this data? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyp
Max,
This works for most of my data but I have some data that has alpha characters in them where this formula fails. Can you or FSt1 offer an adaptation to this formula that allows for alpha characters? Here's a few more examples ABA-12345-12345 ABA-C12345-C12345 ABC-18L123-18L123 ABC-Q456789-Q456789 "Max" wrote: Presuming your data in A1 down looks like this: ABC-12345-12344 ABC-12345-12355 etc In B1: =MID(A1,SEARCH("-",A1,SEARCH("-",A1)+1)+1,99)+0 Copy down to return the numbers after the 2nd hyphen as real numbers, viz: 12344 12355 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "brantty" wrote: I have a column of cells that have excess data that needs removed. The #of characters vary. I'm needing a formula to delete all the characters that precede the 2nd hyphen as well as delete the 2nd hyphen. Ex. [ABC-12345-12345] I need the cell to read [12345] Can anyone provide a formula that can remove this data? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyp
Here's a few mo
Here's a few more examples ABA-12345M-12345M ABA-C12345-C12345 ABC-18L123-18L123 ABC-Q456789JJ-Q456789JJ "FSt1" wrote: hi, you said the number of characters vary. could you supply more examples of the data? Regards FSt1 "brantty" wrote: I have a column of cells that have excess data that needs removed. The #of characters vary. I'm needing a formula to delete all the characters that precede the 2nd hyphen as well as delete the 2nd hyphen. Ex. [ABC-12345-12345] I need the cell to read [12345] Can anyone provide a formula that can remove this data? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyp
And one more example that has a zero.
ABC-07901-07901 I need the zero to stay with the number, thus the result to be: 07901. thanks for your help "FSt1" wrote: hi, you said the number of characters vary. could you supply more examples of the data? Regards FSt1 "brantty" wrote: I have a column of cells that have excess data that needs removed. The #of characters vary. I'm needing a formula to delete all the characters that precede the 2nd hyphen as well as delete the 2nd hyphen. Ex. [ABC-12345-12345] I need the cell to read [12345] Can anyone provide a formula that can remove this data? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyphen
On Thu, 3 Apr 2008 17:16:24 -0700, brantty
wrote: I have a column of cells that have excess data that needs removed. The #of characters vary. I'm needing a formula to delete all the characters that precede the 2nd hyphen as well as delete the 2nd hyphen. Ex. [ABC-12345-12345] I need the cell to read [12345] Can anyone provide a formula that can remove this data? Assuming that your brackets [ ] are not really part of the value, then: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),2))+1,255) will return everything after the 2nd hyphen. --ron |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyphen
If you always want to keep the characters after the last (no matter how many)
hyphen... And you don't have to use a formula, you could use: Select the range to fix edit|replace what: *- (asterisk, hyphen) with: (leave blank) replace all brantty wrote: I have a column of cells that have excess data that needs removed. The #of characters vary. I'm needing a formula to delete all the characters that precede the 2nd hyphen as well as delete the 2nd hyphen. Ex. [ABC-12345-12345] I need the cell to read [12345] Can anyone provide a formula that can remove this data? -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyp
Ron, You are awesome. Great work. Its working exactly as I needed.
Ty "Ron Rosenfeld" wrote: On Thu, 3 Apr 2008 17:16:24 -0700, brantty wrote: I have a column of cells that have excess data that needs removed. The #of characters vary. I'm needing a formula to delete all the characters that precede the 2nd hyphen as well as delete the 2nd hyphen. Ex. [ABC-12345-12345] I need the cell to read [12345] Can anyone provide a formula that can remove this data? Assuming that your brackets [ ] are not really part of the value, then: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),2))+1,255) will return everything after the 2nd hyphen. --ron |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyp
Try this:
=MID(A1,FIND("-",A1,FIND("-",A1)+1)+1,255) -- Biff Microsoft Excel MVP "brantty" wrote in message ... And one more example that has a zero. ABC-07901-07901 I need the zero to stay with the number, thus the result to be: 07901. thanks for your help "FSt1" wrote: hi, you said the number of characters vary. could you supply more examples of the data? Regards FSt1 "brantty" wrote: I have a column of cells that have excess data that needs removed. The #of characters vary. I'm needing a formula to delete all the characters that precede the 2nd hyphen as well as delete the 2nd hyphen. Ex. [ABC-12345-12345] I need the cell to read [12345] Can anyone provide a formula that can remove this data? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyp
Might help u
If A1 = ABA-12345M-12345M IN B1 =MID(A1,SEARCH("-",A1,SEARCH("-",A1,SEARCH("-",A1)+1))+1,99) On Apr 4, 7:10*am, brantty wrote: Here's a few mo Here's a few more examples ABA-12345M-12345M ABA-C12345-C12345 ABC-18L123-18L123 ABC-Q456789JJ-Q456789JJ "FSt1" wrote: hi, you said the number of characters vary. could you supply more examples of the data? Regards FSt1 "brantty" wrote: I have a column of cells that have excess data that needs removed. *The #of characters vary. *I'm needing a formula to delete all the characters that precede the 2nd hyphen as well as delete the 2nd hyphen. Ex. * [ABC-12345-12345] I need the cell to read [12345] Can anyone provide a formula that can remove this data?- Hide quoted text - - Show quoted text - |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyp
You just need to remove the "+0" from the earlier,
viz use in B1, copied down: =MID(A1,SEARCH("-",A1,SEARCH("-",A1)+1)+1,99) (All extracts in col B will be in text) Anyway, I see you have since received an answer you like -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "brantty" wrote in message ... Max, This works for most of my data but I have some data that has alpha characters in them where this formula fails. Can you or FSt1 offer an adaptation to this formula that allows for alpha characters? Here's a few more examples ABA-12345-12345 ABA-C12345-C12345 ABC-18L123-18L123 ABC-Q456789-Q456789 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyp
Ron,
I found some cells that were different,. Instead of looking for the 2nd hypen, can you adapt a formula to keep all data after the LAST hyphen? Ty "Ron Rosenfeld" wrote: On Thu, 3 Apr 2008 17:16:24 -0700, brantty wrote: I have a column of cells that have excess data that needs removed. The #of characters vary. I'm needing a formula to delete all the characters that precede the 2nd hyphen as well as delete the 2nd hyphen. Ex. [ABC-12345-12345] I need the cell to read [12345] Can anyone provide a formula that can remove this data? Assuming that your brackets [ ] are not really part of the value, then: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),2))+1,255) will return everything after the 2nd hyphen. --ron |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyp
Try this:
=MID(A1,FIND("~~",SUBSTITUTE(A1,"-","~~",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,255) Assumes there is at least 1 hypen. -- Biff Microsoft Excel MVP "brantty" wrote in message ... Ron, I found some cells that were different,. Instead of looking for the 2nd hypen, can you adapt a formula to keep all data after the LAST hyphen? Ty "Ron Rosenfeld" wrote: On Thu, 3 Apr 2008 17:16:24 -0700, brantty wrote: I have a column of cells that have excess data that needs removed. The #of characters vary. I'm needing a formula to delete all the characters that precede the 2nd hyphen as well as delete the 2nd hyphen. Ex. [ABC-12345-12345] I need the cell to read [12345] Can anyone provide a formula that can remove this data? Assuming that your brackets [ ] are not really part of the value, then: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),2))+1,255) will return everything after the 2nd hyphen. --ron |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyp
Use this
=MID(A1,FIND("-",A1)+1,99) On Apr 4, 8:06*am, brantty wrote: Ron, * I found some cells that were different,. *Instead of looking for the 2nd hypen, can you adapt a formula to keep all data after the LAST hyphen? Ty "Ron Rosenfeld" wrote: On Thu, 3 Apr 2008 17:16:24 -0700, brantty wrote: I have a column of cells that have excess data that needs removed. *The #of characters vary. *I'm needing a formula to delete all the characters that precede the 2nd hyphen as well as delete the 2nd hyphen. Ex. * [ABC-12345-12345] I need the cell to read [12345] Can anyone provide a formula that can remove this data? Assuming that your brackets *[ *] are not really part of the value, then: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),2))+1,255) will return everything after the 2nd hyphen. --ron- Hide quoted text - - Show quoted text - |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyp
On Thu, 3 Apr 2008 19:34:01 -0700, brantty
wrote: Ron, You are awesome. Great work. Its working exactly as I needed. Ty Glad to help. Thanks for the feedback. --ron |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to remove characters in a cell that precede a specific hyp
On Thu, 3 Apr 2008 20:06:01 -0700, brantty
wrote: Ron, I found some cells that were different,. Instead of looking for the 2nd hypen, can you adapt a formula to keep all data after the LAST hyphen? Ty Yep. It's just a matter of changing the "2" (for the 2nd hyphen) to a formula that calculates the number of hyphens: =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1), LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,255) --ron |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hyphens problem
a1 = ABC-12345-12345
ANSWER IS =mid(a1,54,5) and answer = 12345 |
|
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hyphens problem
If that is the answer, then I am confused about what the question is? :-)
-- David Biddulph <Suraj Noorsai wrote in message ... a1 = ABC-12345-12345 ANSWER IS =mid(a1,54,5) and answer = 12345 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Remove Characters Until a Specific Character is Reached | Excel Worksheet Functions | |||
| Remove Characters from a cell | Excel Worksheet Functions | |||
| remove non-numeric characters from a cell | Excel Discussion (Misc queries) | |||
| remove last three characters of cell | Excel Discussion (Misc queries) | |||
| Need to remove hyphen in group of #s | Excel Discussion (Misc queries) | |||
Linear Mode
