Separate figures from Text
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
|