Trim the whole column?

ShantanuDagar

Hi,

I have a data set with columns which needs to be trimmed.

All the data is with the format 123: ABCD EFGH IJKL

Number with a colon and then words.

or XYZ: ABC DEFG HIJKL

word with a colon and then words.

These need to be replaced or trimmed until before the colon, in some columns it will be after the colons.

Replace function ain't very much useful as there is multiple different values which will need to be replaced in a single column and then multiple columns.

Text to columns also I am not looking much into as it will require many tools to go over each column.

Looking for any solution preferably using formula or regex

Thanks

Help

Accepted answers

DataNath

@ShantanuDagar here's a couple of options for both before and after the colon, with and without regex:

Trim columns before or after colon.yxmd

All comments

DataNath

Hey @ShantanuDagar,

These need to be replaced or trimmed until before the colon, in some columns it will be after the colons.

Not quite following, especially as there's no explanation of the logic as to when you'd want to keep what comes before vs after the colon. Can you give some further information on this? Some examples of inputs and what you'd expect the output to look like would be great as well. Thanks.

ShantanuDagar

In 1 column only 1 pattern will be there.

Like in column M:

All the values to be changed to before the colons.

121: Profits

221: Revenue

302: Loss

to be changed to

121

221

302

Then in another column it can be after the colons.

PR: Profts in last month

RN: Revenue in last quarter

to be changed to

Profts in last month

Revenue in last quarter.

So in 1 column, only 1 pattern will exist either before or after.

DataNath

@ShantanuDagar here's a couple of options for both before and after the colon, with and without regex:

Trim columns before or after colon.yxmd

DataNath

@ShantanuDagar if you need the first part (before the colon), use either of the 'Column 1' formulae in my workflow. If you need the latter part (after the colon), you can use either of the 'Column 2' formulae.

If you have multiple fields that you need to extract this kind of information from, you can put the relevant formula into a Multi-Field Formula tool and tick the fields you want to apply it to.

ShankerV

Hi @ShantanuDagar

Please find the below.

(.+):

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703