I am doing data analysis (historical data); however in the name column, i am facing the formatting issue like ["()", "extra space", "-" ]. When i try to do analysis, each name goes as a separate entity and couldn't able to make proper analysis. How to bring all the name in the same basket. Thanks in advance.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Text Analysis
- Thread starter rkristy
- Start date
Special-K99
Well-known Member
- Joined
- Nov 7, 2006
- Messages
- 8,511
- Office Version
- 2019
Examples of data and your expected output?
Upvote
0
Milk 2 litres
Milk 2 Litres
Milk - 2 litres
Milk (2 litres)
Output: I want to bring all in one basket, else each one goes as separate one. Even in pivot or in any form of calculations. This is one example, similarily I have for many items & it is not easy to manually correct this as I have and will receive 20k+ transactations each time. Thanks!
Upvote
0
mikerickson
MrExcel MVP
- Joined
- Jan 15, 2007
- Messages
- 24,355
I would first clean the data, remove all seperators (like (- etc) and use TRIM to remove double spaces.
Then analyze that cleaned data.
Formulas like =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")"-","") would help
Then analyze that cleaned data.
Formulas like =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")"-","") would help
Last edited:
Upvote
0
