123Mike
4654Steve
76Joe
745937David
what excel formula is needed to pull the names. and what formula is needed to pull the number?
2007-10-01 11:01:11 · 3 answers · asked by Anonymous in Computers & Internet ➔ Software
Well, it's not pretty, but here it is:
If the text to be separated is in A1, these formulas will pull out the name and the number:
name: =IF(ISNUMBER(VALUE(MID(A1, 2,1))), IF(ISNUMBER(VALUE(MID(A1, 3,1))), IF(ISNUMBER(VALUE(MID(A1, 4,1))), IF(ISNUMBER(VALUE(MID(A1, 5,1))), IF(ISNUMBER(VALUE(MID(A1, 6,1))), RIGHT(A1,LEN(A1)-6), RIGHT(A1,LEN(A1)-5)), RIGHT(A1,LEN(A1)-4)), RIGHT(A1,LEN(A1)-3)), RIGHT(A1,LEN(A1)-2)), RIGHT(A1,LEN(A1)-1))
If the name formula above is in C1:
number: =LEFT(A1,SEARCH(C1,A1)-1)
The number formula will return the number in text format. If you need it to be in number format, use this:
=VALUE(LEFT(A1, SEARCH(C1,A1)-1))
These will work if the number is between 1 and 6 digits.
If you need more info, add more details, or e-mail.
Cheers.
.
2007-10-01 13:42:48 · answer #1 · answered by aladou 5 · 0⤊ 0⤋
try to use the function which searsh the position of the symbol and then the function wich back the fixed number of symbols. It is pissible to do, but it would be a lot of nested functions.
2007-10-01 18:22:50 · answer #2 · answered by Anonymous · 0⤊ 0⤋
you need visual basic to do that for you.... i think excel cant do that alone....
2007-10-01 18:05:43 · answer #3 · answered by Anonymous · 0⤊ 1⤋