On Sep 27, 3:57*pm, "Rick Rothstein"
wrote:
If you are willing to use worksheet formula solutions...

Number-Space-Name
========================
=REPLACE(SUBSTITUTE(TRIM(A1),"~"," "),1,1,"")

Letter-Number-Space-Name
=========================
=SUBSTITUTE(TRIM(A1),"~"," ")

Change A1 to the actual cell containing your first piece of data and then
copy the formula down.

--
Rick (MVP - Excel)

"Stav19" wrote in message

...



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!- Hide quoted text -

- Show quoted text -

Hi mark

thanks for getting back to me! That's pretty much what i'd be after
for VBA, but want to try and "keep it simple" although I'm pretty sure
I'm not!!!

Pete