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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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,985
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,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top