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!

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!

G'day Stav

Don Guillett provided this aswer in another thread, it may help you.

Sub replaceem()
lr=cells(rows.count,"F").end(xlup).row
myarray = Array("-", "(", ")")
For Each i In myarray
Range("f4:f" & lr).Replace i, "", LookAt:=xlPart
Next i
End Sub

Try changing it to something like:

Sub Convert_To_Space()
lr=cells(rows.count,"A").end(xlup).row
myarray = Array("~")
For Each i In myarray
Range("A3:A" & lr).Replace i, "", LookAt:=xlPart
Next i
End Sub

HTH
Mark.

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!

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

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 Rick

Both of those work great, and are simpler than mine! Thanks for your
help

Pete