remove character in excel 2000 column

markbarbieri · #1 (**permalink**) 01-09-04, 02:42

HELP
I have 24,000 rows that have zip codes in them and the zip start with a ' in fron of it. eg. '98001
I need to remove the '
what do I do? I know very little about excel, do I need to do a function or create a module?
Thanks in advance

Philem · #2 (**permalink**) 01-09-04, 12:18

Quote:

Originally posted by markbarbieri
HELP
I have 24,000 rows that have zip codes in them and the zip start with a ' in fron of it. eg. '98001
I need to remove the '
what do I do? I know very little about excel, do I need to do a function or create a module?
Thanks in advance

Hullo. I'd recommend using a second column, to hold the new zips. Say you've got the zips in A. In B, use this formula

Code:

=RIGHT(a1,5)

. Copy that down the length of your source, and that will give you a "stripped" zip code. Then, if you'd like, you can Copy->Paste Special->Values over the old info in A.

HTH

P

markbarbieri · #3 (**permalink**) 01-10-04, 15:22

That didn't work, when I paste special > values it puts the ' back in.

Quote:

Originally posted by Philem
Hullo. I'd recommend using a second column, to hold the new zips. Say you've got the zips in A. In B, use this formula

Code:

=RIGHT(a1,5)

. Copy that down the length of your source, and that will give you a "stripped" zip code. Then, if you'd like, you can Copy->Paste Special->Values over the old info in A.

HTH

P

Xl-Dennis · #4 (**permalink**) 01-10-04, 18:15

Hi,

If this is a one-time action only then I suggest to do the following:

1. Sleect the column by clicking on the column-head (grey-cell)
2. Choose the command Edit | Replace...
3. Select the tab Replace
4. Int eh field for Find what enter '
5. Leave the field for Replace with empty.
6. Click on the OK-button.
7. Done!

Let us know the outcome of above.

Kind regards,
Dennis

markbarbieri · #5 (**permalink**) 01-10-04, 22:01

First thing I tried......, it's like magic, it doesn't work on ' in front of letters or numbers. Try it, put '97001 in a cell and then try and remove it with replace or even try the other suggestion. Nothing is working. Please don't tell me I have to manually delete the ' in every cell.
Any Microsoft Excel programmers out there that can help? Any masters of Excel out there?? God???

Quote:

Originally posted by Xl-Dennis
Hi,

If this is a one-time action only then I suggest to do the following:

1. Sleect the column by clicking on the column-head (grey-cell)
2. Choose the command Edit | Replace...
3. Select the tab Replace
4. Int eh field for Find what enter '
5. Leave the field for Replace with empty.
6. Click on the OK-button.
7. Done!

Let us know the outcome of above.

Kind regards,
Dennis

Xl-Dennis · #6 (**permalink**) 01-11-04, 06:44

Hi,

I dont think God has something to do with it and I believe it does not require any XL-Master but anyway here we go:

Code:

Option Explicit

Sub Delete_Char()
Dim wsSheet As Worksheet
Dim rnData As Range
Dim vaData As Variant
Dim i As Long

Set wsSheet = ActiveSheet

With wsSheet
    'Here You change it to Your specific range
    Set rnData = .Range(.Range("A2"), .Range("A65536").End(xlUp))
End With

vaData = rnData.Value

For i = LBound(vaData) To UBound(vaData)
    vaData(i, 1) = Right(vaData(i, 1), vaData(i, 1) - 1)
Next i

rnData = vaData
    
End Sub

At least it works in my enviroment.

Kind regards,
Dennnis

markbarbieri · #7 (**permalink**) 01-11-04, 15:34

Thanks Dennis! Just in time to, this puppy is due tomorrow.

Quote:

Originally posted by Xl-Dennis
Hi,

I dont think God has something to do with it and I believe it does not require any XL-Master but anyway here we go:

Code:

Option Explicit

Sub Delete_Char()
Dim wsSheet As Worksheet
Dim rnData As Range
Dim vaData As Variant
Dim i As Long

Set wsSheet = ActiveSheet

With wsSheet
    'Here You change it to Your specific range
    Set rnData = .Range(.Range("A2"), .Range("A65536").End(xlUp))
End With

vaData = rnData.Value

For i = LBound(vaData) To UBound(vaData)
    vaData(i, 1) = Right(vaData(i, 1), vaData(i, 1) - 1)
Next i

rnData = vaData
    
End Sub

At least it works in my enviroment.

Kind regards,
Dennnis

Internet Services

Web Development

Digital Marketing

Consumer Tech