Posted: 8/19/2011 11:52:23 AM EDT
|
I cannot figure this one out.
I need to remove specific dashes from a very large set of numbers. Example: Original 234-345-456-67899 I need: 234-345-45667899 Or something along those lines. The =substitute formula only works universally as far as I know, which removes all dashes. I wish to retain specific dashes, while removing others. Is there a way this can be accomplished? Don't let me down, Arfcom. |
|
Not sure if the numbers are all the same length but you could use the mid function to break it apart and then concantonate to put it back together.
=(Concantonate(mid(A1,1,11)&mid(13,5)) A1 is where the number is. 1 means you want the start of the series. 11 means you want through the 11th diget. 13 means you want the start of the 13 diget after the last dash. 5 means you want 5 digets after the 13th diget. edit-if the number of numbers between the digets changes you could do a text to column where a dash is the delimiter. Go to Data and Text to Column on the pulldown menu. Click on Delimited and then next. It will be separated. Use the =A1&"-"&A2&"-", ect to put it back together. The "-" tells it to put dash as text back into amounts you wanted the dash to stay in. Kind of a carpetbombing and then treat the civillians you weren't aiming for approach. |
|
If all the numbers are in the same format (i.e., same number of dashes), use Convert Text To Columns to break each set of numbers between the dashes into its own column. Then use another formula to concatenate the cells together like you want: =A1 & "-" & B1 & "-" & C1 & D1
|
| i often use "replace all" to edit calenders i use to track our MRI & CT scanner volume, like when i go from march to april i replace 3/ with 4/, then i have to manually edit the 3rd, 13th, & 23rd which it has changed to be 4th 14th & 24th. there might be a better way to do it, but i don't know of one |
|
Quoted: I cannot figure this one out. I need to remove specific dashes from a very large set of numbers. Example: Original 234-345-456-67899 I need: 234-345-45667899 Or something along those lines. The =substitute formula only works universally as far as I know, which removes all dashes. I wish to retain specific dashes, while removing others. Is there a way this can be accomplished? Don't let me down, Arfcom. =LEFT(cell,11)&RIGHT(cell,5) This will only work if all of the cells are formatted exactly the same. Otherwise use the text to columns advice a few replies up. |