what is the farmula to saprate nums and char in a cell

[mahesh kumar gupta]

problem is that

fsh45fdh8fd
rfas85412
r321s845

i want the following results
fshfdhfd 458

rfas 85412


rs 321845

what is the farmula to saprate nums and char in a cell

[RudiS]

Hi, welcome to the forum :0

See this...

[Glenn Kennedy]

Rudi's formula works, except that if there's a zero in the sequnce, it appears in the text column and the numbers column. Add another nest of substitutes and it works perfectly.

Please Login or Register  to view this content.

Is there a more elegant way of doing this, though??

[RudiS]

Hi Glen,

TX for that addition.
As for a more elegant way....I'd say by using VBA and creating a user defined function.

[icestationzbra]

a couple of VBA methods...

[Tony Valko]

Here are a couple of formulas (with limitations).

Data Range

	A	B	C
2	fsh45fdh8fd	fshfdhfd	458
3	rfas85412	rfas	85412
4	r321s845	rs	321845

The length of the string must be <=300 characters.

The total number of digits in the string must be <=14 digits.

Note that the extracted numbers are a TEXT string even though they look like numbers. This is necessary to keep any leading zeros.

This will not work in Excel versions 2003 and earlier.

Create this named formula.
Goto the Formulas tab>Define name
Name: Array
Refers to: =ROW(INDIRECT("A1:A300"))
OK out

Enter this formula in B2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")

Enter this array formula** in C2:

=MID(SUMPRODUCT(--MID("01"&A2,SMALL((Array-1)*ISNUMBER(-MID("01"&A2,Array,1)),Array)+1,1),10^(300-Array)),2,300)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Select B2:C2 and copy down as needed.

These formula may work but I think something like this is best done using VBA functions.