Formula/Code to remove first 3 numbers and dash

[Legend Rubber]

I have 550 cells that contain varied numbers in the same format as phone numbers... ###-###-####

I would like to remove the first ###-

Is this doable easy??

[TMS]

One way, for data in cell A1:

Please Login or Register  to view this content.

Regards, TMS

[Glenn Kennedy]

Try this...
=MID(A1,FIND("-",A1)+1,255)

[TMS]

Other variations on a theme:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Regards, TMS

[Legend Rubber]

Please Login or Register  to view this content.

This worked great, but can it be adapted to also remove the last -####? So that only the middle 3 numbers are left? so for example 123-456-7891 would turn to 456?

[Legend Rubber]

also there is sometimes more numbers after those four... it could be 123-456-7891.36524 or something... so if it could be that EVERYTHING after the last "-" is removed as well...

[TMS]

Kinda moving the goal posts a little, no?

You have been given 4 possible approaches to address the original question.

Maybe you should have a think about all the variations you might encounter and come back with a sample workbook that solutions can be tested on.

If you just want the 8 digits after the first hyphen and the structure of the data is consistent ... 3 digits hyphen 3 digits hyphen lots of digits ... then modify one of the MID formula already provided.

Please Login or Register  to view this content.

[Legend Rubber]

ya thanks... i am sorry, i didn't realize we weren't allowed to alter our request after original post.

This is for two separate scenarios... i am already using the original solution in my first workbook, and then the idea came to use it in another place on a second workbook...

Unfortunately i do not know a lot about excel, and i did not think of using the second scenario until i put the first in place...

Thanks for all your help.

P.S. Manchester is a sweet place, went there on my last trip to England. The tunnels where especially interesting.

[TMS]

No problem. It's not so much that you can't change your request, more a case of avoiding "scope creep" where the requirements change and evolve as each question is answered.

The best approach is, when the original question is answered, mark it solved and draw a line under. Start a new thread with the new question/variation and, if it is appropriate, provide a link back to the earlier question for reference. That way, you get more/new people looking at your question and they can benefit from the solution(s) already provided.

With regard to the tunnels, I've lived here all my life and never been down those tunnels. Unfortunately, tours are currently suspended or it might have prompted me to go. We very recently visited London and went on a tour of the "Lost Rivers of London". In this case, following the route of the River Fleet. Very interesting, in some senses, but more a walk round the back streets of London looking at grids (drains). Oh, and the highlight being the Travel Lodges of London . Still, it wasn't expensive

Regards, TMS

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.