Removing text after numbers

[Kingkoopa]

Hi Guys,

Using windows 7 and excel 2007.

Reposting this I think there was a forum roll back?? Couldn't log in the whole afternoon kept giving a 404 error and found my thread had disappeared.


I have the following set of data :

Apple 330grams

Apple 29 kilo

Pear 21 baskets

Pear & Apples 12 total

<tbody>
</tbody>

I would like to remove all the text after the numbers. Thus I would get the following results :

Apple 330
Appel 29
Pear 21
Pear & Apples 12

Any idea guys?

I saw someone posting the solution but its gone now when I try to search back the thread .


Thank you

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG07Dec14
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]For[/COLOR] n = 1 To Len(Dn.Value)
    [COLOR="Navy"]If[/COLOR] Dn.Characters(n, 2).Text Like "[0-9][ ]" [COLOR="Navy"]Then[/COLOR]
        Dn.Value = Mid(Dn.Value, 1, n + 1)
    [COLOR="Navy"]ElseIf[/COLOR] Dn.Characters(n, 2).Text Like "[0-9][a-z]" [COLOR="Navy"]Then[/COLOR]
        Dn.Value = Mid(Dn.Value, 1, n)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick