How to remove specific data (words, numbers,etc. - not the same each time) from a column

Hello All,

Thank you in advance for your help!

I have a column that has data that needs to be removed from certain cells, however the problem is that these data are not the same in each row. Please see below an example:

Column as is Now	Column I need it to be
3	3
2	2
xxx, uyq: 9 and yyy:4	9
zzz: 5 and ddd: 1	5
6	6
1 / 10,000	1

Topic of Interest

Accepted answers

zajaccount

That's quite a messy dataset! I added a filter in the beginning to check for empty/null cells in the column - the workflow runs for the "False" leg, while the "True" leg is unionend with the summary at the end - it adds however many blank rows there were in the input file.

If this works, please accept it as the solution.

Book1.xlsx

New Workflow1.yxmd

All comments

zajaccount

Hi Arieta,

could you explain the issue in more detail please? Is there any specific logic as to what data should be in the final output?

For instance in this example:

xxx, uyq: 9 and yyy:4, why are we choosing 9?

zzz: 5 and ddd: 1 and here, why 5 and not 1?

Is it perhaps always the first number that appears in the cell?

arieta

Hello,

Thank you very much for your reply.

I should always use the higher number that appears in the cell and remove the text in each cell and the rest of the numbers or characters.

zajaccount

Hi,

then in this case:

1 / 10,000

why do we take the 1 instead of 10,000?

arieta

Hello,

This represents that 1 is divided by 10,000, however in my case there is no need to have the 10,000 in the column so I want 10,000 to get removed as well.

In my column, there are cases of: 1-5 /10,000. So, in this case I only need to keep the 5, as this is the highest number (we don't get into consideration the 10,000 as we assume the division in the column title).

Hope this was helpful!

Thank you once again.

zajaccount

To be honest I have some troubles imagining what your dataset looks like.

Are the cell options limited to certain cases?

For instance, I understand that you may have fields where:

- only 1 number occurs and this number should come out as the desired output

- strings and several numbers occurr - in this case, strings should be removed and the highest number should be taken into consideration

- fractions occurr - in this case, the denominator (ii.e. the value "after /" should be removed and the higher number from the numerator should be considered

Are the scenarios limited to these options?

arieta

Hello,

Exactly, these are the possible scenarios. So, my desired output is to get a column only with numbers (the highest number in each cell)

Thank you!

zajaccount

Hi,

in this case, this workflow should do the job - let me know what you think.

Kind regards

New Workflow1.yxmd

Book1.xlsx

arieta

Hello,

Thank you for your help!

Unfortunately, this does not work as sometimes the numbers contain decimal places. For example, there are cases that the numbers are 3.1, 0.002, 2.05, etc.

zajaccount

Hi,

I adjusted the workflow. I tested it on the following examples:

and it worked - please let me know if you have any other edge cases where it fails.

New Workflow1.yxmd

Book1.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703