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

[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.

[ShankerV]

Hi @ShantanuDagar 

Please find the expected output.

(:\s.+)

Input was:

[DataNath · Accepted solution]

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

[ShankerV]

Hi @ShantanuDagar 

Please find the expected output for next one.

(.+:\s)

Input was:

Hope it helps!!!!

Many thanks 

Shanker V

[ShantanuDagar]

Thanks.

And what if I need the latter part in output instead the first.

[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 

If you need the result to be in new column. This will help.

Regular Expression for 1st Regex
(\d+):

Regular Expression for 2nd Regex
.+:\s(.+)

Many thanks

Shanker V

[ShankerV]

Hi @ShantanuDagar 

Please find the below.

(.+):