Dear Mate,

I have some 1000+ data which has a mix of text and figues combined. Each
combined entry is in one cell. I need to separate them or remove the figures
from the text for examaple.

4357890Robetson & company
6578David Co. Ltd
1234bedrock ltd


Regards

Mat

ASAP Utilities, a free add-in available at www.asap-utilities.com has a
feature that will delete all numeric characters from strings..........

Vaya con Dios,
Chuck, CABGx3



"Mat" wrote:

Dear Mate,

I have some 1000+ data which has a mix of text and figues combined. Each
combined entry is in one cell. I need to separate them or remove the figures
from the text for examaple.

4357890Robetson & company
6578David Co. Ltd
1234bedrock ltd


Regards

Mat

As long as the only digits that appear in the string are at the front, you could
put this in B1 (with A1 containing the original string):

=MAX(IF(ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),
ROW(INDIRECT("1:"&LEN(A1)))))

(but hit ctrl-shift-enter instead of just enter.)

And put this in C1:
=--LEFT(A1,B1)

And put this in D1:
=MID(A1,B1+1,len(a1))

Then drag those formulas down the length of your data.

But this will fail when you have this:

4357890Robertson & 2 Sons and 3 Daughters & Company

Mat wrote:

Dear Mate,

I have some 1000+ data which has a mix of text and figues combined. Each
combined entry is in one cell. I need to separate them or remove the figures
from the text for examaple.

4357890Robetson & company
6578David Co. Ltd
1234bedrock ltd

Regards

Mat

--

Dave Peterson

Dear Dave,

Excellent Formula it worked perfectly.

Regards

Mat

"Dave Peterson" wrote:

As long as the only digits that appear in the string are at the front, you could
put this in B1 (with A1 containing the original string):

=MAX(IF(ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),
ROW(INDIRECT("1:"&LEN(A1)))))

(but hit ctrl-shift-enter instead of just enter.)

And put this in C1:
=--LEFT(A1,B1)

And put this in D1:
=MID(A1,B1+1,len(a1))

Then drag those formulas down the length of your data.

But this will fail when you have this:

4357890Robertson & 2 Sons and 3 Daughters & Company

Mat wrote:

Dear Mate,

I have some 1000+ data which has a mix of text and figues combined. Each
combined entry is in one cell. I need to separate them or remove the figures
from the text for examaple.

4357890Robetson & company
6578David Co. Ltd
1234bedrock ltd

Regards

Mat

--

Dave Peterson

On Thu, 21 Dec 2006 11:11:02 -0800, Mat wrote:

Dear Mate,

I have some 1000+ data which has a mix of text and figues combined. Each
combined entry is in one cell. I need to separate them or remove the figures
from the text for examaple.

4357890Robetson & company
6578David Co. Ltd
1234bedrock ltd


Regards

Mat

It's not entirely clear exactly what you want to do.

There is a difference between "separating them" and "remove the figures from
the text".

In any event, if you download and install Longre's free morefunc.xll add-in
from http://xcell05.free.fr

you can use the formula:

=REGEX.SUBSTITUTE(A1,"\d")

to return the string with all the digits removed.

If you want to separate the text and figures, you could use the formula:

=REGEX.SUBSTITUTE(A1,"\D")

to return just the digits (and the first formula to return the string without
the digits).

If you want something else, please be more specific.


--ron

User defined function OK with you?

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

Usage is: =DeleteNonNumerics(cellref)

If you care to preserve the original, just copy the column and use the UDF on
that copy.


Gord Dibben MS Excel MVP

On Thu, 21 Dec 2006 11:11:02 -0800, Mat wrote:

Dear Mate,

I have some 1000+ data which has a mix of text and figues combined. Each
combined entry is in one cell. I need to separate them or remove the figures
from the text for examaple.

4357890Robetson & company
6578David Co. Ltd
1234bedrock ltd


Regards

Mat