Text Analysis

rkristy

New Member
Joined
Feb 20, 2019
Messages
2
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.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

rkristy

New Member
Joined
Feb 20, 2019
Messages
2
Examples of data and your expected output?
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!
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,550
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,006
Messages
5,465,965
Members
406,457
Latest member
Pinky Rose Jordan

This Week's Hot Topics

Top