How to remove dashes in Social Security number
Suppose you have the SS# in ColA. Insert a column between ColA and ColB. Then
enter this formula in ColA cell 1
=SUBSTITUTE(A1,"-",)
If this post helps click Yes
---------------
Jacob Skaria
"George" wrote:
Where do I insert this formula ?
Thanks- Sorry for the trouble...
"Jacob Skaria" wrote:
Insert a column and use the below formula. Copy down as required
=SUBSTITUTE(D1,"-",)
If this post helps click Yes
---------------
Jacob Skaria
"George" wrote:
That works but it drops any leading zeros from SS# that start with zero !
Thanks
"Eduardo" wrote:
Hi George,
Highlight the column where you have the numbers
Press CTRL H, find what put the dash, replace with leave blank, replace all
"George" wrote:
John - That works but it drops any leading zeros,
plus I am in the Air Force and they don't like Macros
running on their system without them proofing them.
How can I get Jacob's suggestion to work ?
Thanks
"john" wrote:
Jacobss method easiest but if you want macro following should work.
I have used Column D but you will need to amend as required
Sub ReplaceDashes()
Columns("D:D").Replace What:="-", _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End Sub
--
jb
"George" wrote:
Each month I receive an excel spreadsheet and one of the columns has social
security numbers with the dashes. I have to import it into Access without the
dashes, how can I remove the dashes from the social security numbers in Excel
- without doing it by hand ? Is there a way to use format cells to clear the
dashes ?
Thanks
|