Formatting problem
On Sep 27, 2:21*pm, Stav19 wrote:
Hi All
I've got about 1000 rows or so of entries which have the following
format in each cell:
(20 spaces at least)A1234567890~Name
What I would like is to have two version of the formula where I have
just the no. and name but without the "~" and a " " there instead. The
other version would be to have the same as above but with the letter
as well, ie
"A1234567890 Name"
What I have so far:
=REPT("",LEN(LEFT(A3,FIND("~",A3)-1))-
LEN(TRIM(LEFT(A3,FIND("~",A3)-1))))&MID(A3,FIND("~",A3)+1,500) - Name
only but no number
=TRIM(LEN(LEFT(A3,FIND("~",A3)+1))-
LEN(TRIM(LEFT(A3,FIND("~",A3)+1)))&SUBSTITUTE(A3," ~"," ")) = 20
I'm still getting to grips with this, so if someone could point me in
the right direction, I'd be grateful!
Cheers!
|