Really stuck with the following problem

[jeroenvdb]

i need to seperate the following into:

[pre]

A1                B1         C1
OrangeMobile      0.4095     0.399
OtherMobile       0.42       0.41
SunriseMobile     0.395      0.39
the text is in a1 as follows

OrangeMobile0.40950.399

OtherMobile0.420.41

SunriseMobile0.3950.39


it also needs to work for:

A1                   B1
Algeria-Mobile       0.2536
AmericanSamoa        2.234
Andorra              0.1449
Andorra-Mobile       0.8272

[/pre]
the text is the same as above in a1

Algeria-Mobile0.2536

AmericanSamoa2.234

Andorra0.1449

Andorra-Mobile0.8272


does anyone have a formula to do this???

I have 10 tabs with about 650 of such text (i have done it manually for the first two but it took me 4 hours so far)

 

[Luke M]

Formula in B2:

=LEFT(A2,FIND(".",A2)-2)

Copy down as needed


formula in C2:

=IF(COLUMNS($C2:C2)>LEN($A2)-LEN(SUBSTITUTE($A2,".","")),"",VALUE(MID($A2,FIND(CHAR(9),

SUBSTITUTE($A2,".",CHAR(9),COLUMN(A$1)))-1,FIND(CHAR(10),

SUBSTITUTE($A2,".",CHAR(10),COLUMN(B$1))&" "&CHAR(10))-FIND(".",$A2))))


Copy to the right and down as needed.


Fun challenge...definitely a tough one to figure out. =)

 

[imgonna]

Hi

(Lukes is more elegant though)


In B1:

=LEFT(A1,FIND(".",A1)-2)

copy down


In C1:

=LEFT(MID(B1,FIND(".",B1,2)-1,100),(LEN(MID(B1,FIND(".",B1,2)-1,100))-LEN(D1)))

and copy down


In D1:

=IF(ISERROR(MID(MID(MID(A1,FIND(".",A1,2)-1,100),3,100),FIND(".",MID(MID(A1,FIND(".",A1,2)-1,100),3,100),2)-1,100)),"",

MID(MID(MID(A1,FIND(".",A1,2)-1,100),3,100),FIND(".",MID(MID(A1,FIND(".",A1,2)-1,100),3,100),2)-1,100))

and copy down


Ditto - nice problem

 

[NARAYANK991]

Hi Luke ,


Can you check if your formula works when there are more than 2 numbers in each text string e.g.


Algeria-Mobile0.25360.30560.101010.111170.2345


The following seems to work for even this :


=IF(COLUMNS($C2:C2)>LEN($A2)-LEN(SUBSTITUTE($A2,".","")),"",VALUE(MID($A2&".",FIND(CHAR(9),SUBSTITUTE($A2&".",".",CHAR(9),COLUMN(A$1)))-1,FIND(CHAR(9),SUBSTITUTE($A2&".",".",CHAR(9),COLUMN(B$1)))-FIND(CHAR(9),SUBSTITUTE($A2&".",".",CHAR(9),COLUMN(A$1)))+1)))


Only one assumption is needed : after the first number , all succeeding numbers will have a 0 ( zero ) in front of the decimal point.


Narayan

 

[Luke M]

Hi Narayan,


I was trying to do that, but you are correct, my formula fails. The last FIND function only looks for first decimal points. Oops!

Formula to handle multiple numbers, no assumption about 0, and shorter than Narayan's formula by 3 characters. =P

=IF(COLUMNS($C2:C2)>LEN($A2)-LEN(SUBSTITUTE($A2,".","")),"",VALUE(MID($A2,FIND(CHAR(9),

SUBSTITUTE($A2,".",CHAR(9),COLUMN(A$1)))-1,FIND(CHAR(10),

SUBSTITUTE($A2,".",CHAR(10),COLUMN(B$1))&" "&CHAR(10))-FIND(CHAR(9),

SUBSTITUTE($A2,".",CHAR(9),COLUMN(A$1))))))


I think I even managed to stay under the XL2003 7 embedded function limit.

 

[NARAYANK991]

Hi Luke ,


Great.


Narayan