Formula or Function to Find "blank cells" with " ' "; Replace with true Blank Cells

[Hollando]

Hello all,

Although, I was working this into a Macro, I was wondering if anyone had a non-vba solution as well? Basically, I have cells with " ' " due to an extract from our ERP system here at the office. As you can imagine this is quite irritating.

My question: Is there a way to identify cells with " ' ", the remove the " ' " to create a true blank cell.


Any guidance would be greatly appreciated.

Regards,

Hollando

 

[Comfy]

You can use the built in Find and Replace function

 

[Hollando]

You can use the built in Find and Replace function

Yes, but what happens when I have a character string within a cell that just so happens to have ' within it. Wouldn't this remove this as well?

 

[Comfy]

Not if you expand the options and tick "Match entire cell contents"

 

[Joe4]

In my testing, if there is just a single quote mark, and nothing else in the cell, "Find and Replace" does not seem to identify it/remove it.
However, if all your entries are text, you can use "Data -> Text To Columns" to remove them (caveat: you can only do one column at a time).

Just perform the following steps:
1. Highlight your column
2. From the Data menu, select Text to Columns
3. Click "Next" twice to get to Step 3.
4. Select the "Text" radio button
5. Click "Finish"

 

[Hollando]

Not if you expand the options and tick "Match entire cell contents"

Unfortunately, even with the Match entire cell contents checked off I can't get the desired effect.

 

[Joe4]

Unfortunately, even with the Match entire cell contents checked off I can't get the desired effect.

Yep, I found that too. Did you try my suggestion above (post #5)?

 

[Hollando]

In my testing, if there is just a single quote mark, and nothing else in the cell, "Find and Replace" does not seem to identify it/remove it.
However, if all your entries are text, you can use "Data -> Text To Columns" to remove them (caveat: you can only do one column at a time).

Just perform the following steps:
1. Highlight your column
2. From the Data menu, select Text to Columns
3. Click "Next" twice to get to Step 3.
4. Select the "Text" radio button
5. Click "Finish"

This worked great!

Now before I create a new thread what if say in columns A, B, C, D, E and F I have a character string that leads with '? Some of these strings are Alpha some are Numeric. The cell format is currently General, so I believe for the Numeric cells I can convert to numbers. Unfortunately, some of the Numeric columns represent UPCs and with UPCs you can have a leading Zero. If that is the case converting to Numbers may do more harm than good.

So basically my ask is how can a rid a cell of the ' when one of the following scenerios has occured 'xxxxxx or '12345678 or '012345678.

 

[Joe4]

If you choose the format as "Text" (step #4), all leading zeroes will be kept. The only caveat is any value entered as a numeric will change to text (those the characters should not change).
As long as you aren't doing any mathemcatical function on those values, that hopefully shouldn't be an issue.

Otherwise, you might have to go with a different approach (VBA perhaps).