A
Access Joe
Using Excel 2007 in Vista
Hey everyone: I download reports into Excel once a month from a third part
app. The report generally lists a bunch of manually entered data. Once in
Excel, one of the major things I have to do is 'clean' the report so
everything is consistent for things like filtering and sorting. I'm hoping
there is some kind of VB code or Macro than can help me.
An example: in a column titled "Name", 'Access Joe' will sometimes be
written as 'Acces Joe' or 'Acess Joe' or 'AccessJo' or 'Accss Jo' (etc.
etc.). In another column title "Site", the CORRECT spelling of "Microsoft"
will sometimes be written as 'MS' or 'Mircosoft' or 'Microsotf'. See -
because this data is ALL manually entered, we come across inconsistencies
like this all the time. Problem is, the 'error' can always be something
different. So a simple 'Find / Replace' won't work.
Does anyone know of a way to tell Excel to do a 'wildcard search' and
replace those values with the Proper name? In other words (for my name
"Access Joe"), can you tell Excel to do a wildcard search in the column for
'Acc*J*' or 'A*J*E' and replace anything that matches with 'Access Joe'?
Same for Site: can you tell it to search for 'M*s*t' or 'Micr*' and replace
everything with 'Microsoft'? I would JUST want it applied to the column I
highlight so it doesn't look within the entire sheet.
ANY assistance you could provide or recommend would be truly appreciated.
Thanks everyone.
Hey everyone: I download reports into Excel once a month from a third part
app. The report generally lists a bunch of manually entered data. Once in
Excel, one of the major things I have to do is 'clean' the report so
everything is consistent for things like filtering and sorting. I'm hoping
there is some kind of VB code or Macro than can help me.
An example: in a column titled "Name", 'Access Joe' will sometimes be
written as 'Acces Joe' or 'Acess Joe' or 'AccessJo' or 'Accss Jo' (etc.
etc.). In another column title "Site", the CORRECT spelling of "Microsoft"
will sometimes be written as 'MS' or 'Mircosoft' or 'Microsotf'. See -
because this data is ALL manually entered, we come across inconsistencies
like this all the time. Problem is, the 'error' can always be something
different. So a simple 'Find / Replace' won't work.
Does anyone know of a way to tell Excel to do a 'wildcard search' and
replace those values with the Proper name? In other words (for my name
"Access Joe"), can you tell Excel to do a wildcard search in the column for
'Acc*J*' or 'A*J*E' and replace anything that matches with 'Access Joe'?
Same for Site: can you tell it to search for 'M*s*t' or 'Micr*' and replace
everything with 'Microsoft'? I would JUST want it applied to the column I
highlight so it doesn't look within the entire sheet.
ANY assistance you could provide or recommend would be truly appreciated.
Thanks everyone.