Need to find special characters, hidden spaces, extra spaces in excel

Need to find special characters, hidden spaces, extra spaces in excel.

I need to prepare list for upload to SAP.
ssblueAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

Could you be more precise, send a dummy?

Regards
0
ssblueAuthor Commented:
PO LONG TEXT
ASSEMBLY, `RELAY
KIT, VALVE REPAIR, VEE-BALL V150,  V200,  V300
GAUGE, PRESSURE, 1"CH
ASSEMBLY, COVER
KIT, REPAIR, z PACKING, SINGLE TFE, 1/2 STEM
BREAKER, MINIATURE, STANDARD CONFIGURATION, AC, 1-POLE CONFIGURATION, TRIP CURVE C, 2 AMP
SEALSTRIP, STL
BLOWER, MEDIUM PRESSURE FLUIDIZING AIR, 7.5 PSI, 200 HP, 3150 ACFM
EXPANSION JOINT, UNIT 1A ID FAN LUBRICATING AND COOLING NON-DRIVE END BEARING, 2.5 PIPE, NON-CODE PIPE CLASS, SST, A106 GR. B FLANGED END, OIL, 250F,, 14PSIG
VALVE , BALL, 3~

I need to clean up this data for upload to SAP.
0
Rgonzo1971Commented:
a dummy with the wanted result would be helpful
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Zeth LarssonCommented:
Would you have only A-Z, a-z, 0-9, space and comma in your text?
May be it's easier for you to say what you want left in your text?
0
ssblueAuthor Commented:
so I may not know what is there.  I know I would want A-Z, a-z, 0-9, space and comma in the text and then there could be ' and " for inches and feet designations. I know I could find these things separately but I was hoping for something that might fix everything all at once.
0
Zeth LarssonCommented:
What type of file is the text in (.xlsx or .xlsm)?
Is the text located in one cell or several cells?
Is the text in one spreadsheet or more than one spreadsheet?
0
Zeth LarssonCommented:
Try the attached file.
It loop through each worksheet in the active workbook, and test each selected range / cell.
You can make selections on several worksheet by
Select a worksheet
Hold down Ctrl and select a range of cells,  more than one range can be selected on each worksheet.
Repeat on next worksheet.
Execute the macro.
The macro clean only selected cells.
Your text sample look like this after my test run:

PO LONG TEXT
ASSEMBLY, RELAY
KIT, VALVE REPAIR, VEEBALL V150, V200, V300
GAUGE, PRESSURE, 1"CH
ASSEMBLY, COVER
KIT, REPAIR, z PACKING, SINGLE TFE, 12 STEM
BREAKER, MINIATURE, STANDARD CONFIGURATION, AC, 1POLE CONFIGURATION, TRIP CURVE C, 2 AMP
SEALSTRIP, STL
BLOWER, MEDIUM PRESSURE FLUIDIZING AIR, 75 PSI, 200 HP, 3150 ACFM
EXPANSION JOINT, UNIT 1A ID FAN LUBRICATING AND COOLING NONDRIVE END BEARING, 25 PIPE, NONCODE PIPE CLASS, SST, A106 GR B FLANGED END, OIL, 250F,, 14PSIG
VALVE , BALL, 3
cleanTheText.xlsm
0
Matt NicholasBusiness AnalystCommented:
Find and Replace pre upload?

For extra spaces in the Find and Replace function search for "  " (which is two spaces) and Replace with " " (single space)

Also use Find and Replace to locate your 'special characters'
0
Zeth LarssonCommented:
I'm sorry ssblue!
Missed the demand on remove extra spaces.
That is corrected in this file.
This script remove all duplicated continues spaces but the last one.
Sample after my test on your text.

PO LONG TEXT
ASSEMBLY, RELAY
KIT, VALVE REPAIR, VEEBALL V150, V200, V300
GAUGE, PRESSURE, 1"CH
ASSEMBLY, COVER
KIT, REPAIR, z PACKING, SINGLE TFE, 12 STEM
BREAKER, MINIATURE, STANDARD CONFIGURATION, AC, 1POLE CONFIGURATION, TRIP CURVE C, 2 AMP
SEALSTRIP, STL
BLOWER, MEDIUM PRESSURE FLUIDIZING AIR, 75 PSI, 200 HP, 3150 ACFM
EXPANSION JOINT, UNIT 1A ID FAN LUBRICATING AND COOLING NONDRIVE END BEARING, 25 PIPE, NONCODE PIPE CLASS, SST, A106 GR B FLANGED END, OIL, 250F,, 14PSIG
VALVE , BALL, 3

Is there more duplicates you want to remove, it's very easy to do that by the same method.
cleanTheText2.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ssblueAuthor Commented:
Thanks guys!
0
Zeth LarssonCommented:
Thanks ssblue!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.