Deleting text after a number with vba

PeterWhitts
PeterWhitts used Ask the Experts™
on
I am using Excel 2003

I am looking for vba code that will look at a cell and delete all spaces and text after the first occuring string of numbers (this could be any random numbers 100.00 or 10,000 or 0.00 or 0  with or without a decimal point).

Therefore if the cell has "Marketing Area 234.0 plus any text which may include a randum number like 99" what I need is "Marketing Area 234.0"

Therefore if the cell has "Book Plot 9,234 plus any extra text which may include a randum number  102 for example" what I need is "Book Plot 9,234"

Many thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
IT Pro/Developer
Top Expert 2008
Commented:
Try this way =GetPartialText(A1)
Public Function GetPartialText(rng As Range) As String
Dim str() As String, s As Variant
Dim result As String

str = Split(rng.Value)

For Each s In str
    result = result & " " & s
    If IsNumeric(Replace(s, ",", ".")) Then Exit For
Next

GetPartialText = Trim(result)

End Function

Open in new window

Jorge PaulinoIT Pro/Developer
Top Expert 2008

Commented:
Place this function in a module

Author

Commented:
Thanks for that but it doesn't work for "Book Plot 9,234.00 plus any extra text which may include a randum number  102 for example".

You get the whole string
in jpaulino's solution change

    If IsNumeric(Replace(s, ",", ".")) Then Exit For
to
    If IsNumeric(Replace(s, ",", "")) Then Exit For

Author

Commented:
Many thanks for the joint answer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial