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!