Attack from the Left

[amerifax]

Phone: 262-255-7430
=LEFT(D2,8)

Result of Cell D2
Phone: 9

I need to capture only the phone number. I want to disregard coming from the right side, because that approach is not 100%. Out of a quarter of 1 million records. I can always count on it looking like "Phone: 262-255-7430"

Therefore I would like to delete the first eight characters and retain the phone number.

Bob

 

[T. Valko]

Phone: 262-255-7430
=LEFT(D2,8)

Result of Cell D2
Phone: 9

I need to capture only the phone number. I want to disregard coming from the right side, because that approach is not 100%. Out of a quarter of 1 million records. I can always count on it looking like "Phone: 262-255-7430"

Therefore I would like to delete the first eight characters and retain the phone number.

Bob

Just find the space, add 1, then take everything to the right of it.

=MID(D2,FIND(" ",D2)+1,25)

 

[amerifax]

Works good. at first I was a little confused about the 25 until I checked out the help file and found that every family that would give me every day after the first position of the space.

Yes, I can see this taking care of many situations that I have to deal with. Thank you very much.
Bob

 

[T. Valko]

Works good. at first I was a little confused about the 25 until I checked out the help file and found that every family that would give me every day after the first position of the space.

Yes, I can see this taking care of many situations that I have to deal with. Thank you very much.
Bob

You're welcome. Thanks for the feedback!

 

[CharlesChuckieCharles]

Phone: 262-255-7430
=LEFT(D2,8)

Result of Cell D2
Phone: 9

I need to capture only the phone number. I want to disregard coming from the right side, because that approach is not 100%. Out of a quarter of 1 million records. I can always count on it looking like "Phone: 262-255-7430"

Therefore I would like to delete the first eight characters and retain the phone number.

Bob

=SUBSTITUTE(D2,"Phone: ","")

 

[Scott Huish]

I would think you would want to get rid of the first 7 characters, not 8. At 8 you are already in the phone number:

=REPLACE(A1,1,7,"")

 

[georgecomstock]

Try this.
Public Function trimleft(mychar As String)
Dim newstring As String
newstring = Right(mychar, Len(mychar) - 7)
trimleft = newstring
End Function

 

[T. Valko]

I interpreted the post to mean there might be ANY text before the phone number.

If the string is ALWAYS of the format: Phone: nnn-nnn-nnnn

Then why can't you simply use:

=RIGHT(D2,12)

Maybe even:

=MID(D2,8,25)

 

[Ron Coderre]

If the cell contents of the 250,000 cells is always of the form:
Phone: ###-###-####

AND...you want to strip the leading text from those cells,
this approach uses no formulas and leaves only the phone numbers in the cells:
• Select the single-column range of cells to be impacted
• Data.Text-to-Columns...Check: Delimited...Click: Next
...Check: Space...Click: Next
...Select the 1st column...Check: Do not import this column
...Click: Finish

That would convert
A1: Phone: 123-456-7890
to
A1: 123-456-7890

Is that something you can work with?

 

[scottylad2]

Or if the numbers always preceded by a space, the numbers in A1

RIGHT(A1,LEN(A1)-FIND(" ",A1))