1

Let's say I have those values in the column:

w 1

w 2

w 3

I want to convert those values to

1

2

3

in another column.

So basically I need to convert the value "w x" to "x".
How do I do that?

Another example: Let's say I have these values in a column:

World Level 1 (AR 0-19)

World Level 2 (AR 20-24)

World Level 3 (AR 25-29)

I want the result to be

1

2

3
5
  • 1
    Welcome. I believe your question could be oversimplified. Are data always, just one letter or more, one digit or more, have a space, also have words etc. Please share some more examples. – marikamitsos Oct 31 '20 at 4:46
  • will add now, going to edit the post – Or24 Oct 31 '20 at 4:53
  • 1
    "Another example: Let's say I have..." These are completely different than your first ones. Do these represent the pattern followed in your column? That is 2 words one digit (something)? – marikamitsos Oct 31 '20 at 5:00
  • Yes. I didn't mention it but there could also be the value "NA" for example which isn't part of the pattern and I don't want to do anything with it basically. Other than that value, yes it's a pattern – Or24 Oct 31 '20 at 5:05
  • Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. – marikamitsos Oct 31 '20 at 5:18
1

There are different ways to handle the issue.
I find the following the easiest ones.

For a single cell you can use

=REGEXREPLACE(A1,"(\(.*\))|(\D)","")*1

For the whole column try

=ArrayFormula(IF(A1:A7="",,REGEXREPLACE(A1:A7,"(\(.*\))|(\D)","")*1))

enter image description here

If you also want to get rid of the #N/A cells, please use

=ArrayFormula(IFERROR(IF(A1:A7="",,REGEXREPLACE(A1:A7,"(\(.*\))|(\D)|""NA""","")*1)))

Functions used:

7
  • How would you manage a cell value of #N/A? – Tedinoz Oct 31 '20 at 5:26
  • @Tedinoz Please see the latest version of the formula. – marikamitsos Oct 31 '20 at 5:36
  • Awesome thanks a lot! Definitely upvoted but it says it wont publicly change the number – Or24 Oct 31 '20 at 5:53
  • Can i just ask what does ((.*))|(\D)","")*1 do? – Or24 Oct 31 '20 at 5:54
  • "Awesome thanks a lot! ". You should accept the answer by clicking on the check sign ✓ (which will go green) on the left of the answer. "Can i just ask..." Please read more about how RE2 syntax works. If still in need please let us know. – marikamitsos Oct 31 '20 at 6:17
0

In the interests of providing an (old-school) alternative. try this formula.

=if(isna(A1),"",trim(mid(A1,search(" ",A1,search(" ",A1,1)+1)+1,search(" ",A1,search(" ",A1,search(" ",A1,1)+1)+1)-search(" ",A1,search(" ",A1,1)+1))))

The logic is that the formula searches for the second and third space: the value between those two spaces being number value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.