S
Stav19
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 No.
and name
I'm still getting to grips with this, so if someone could point me in
the right direction, I'd be grateful!
Cheers!
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 No.
and name
I'm still getting to grips with this, so if someone could point me in
the right direction, I'd be grateful!
Cheers!