Change values in string within text

Question

I have numerous strings with text and number values and need to change their numbers and sort accordingly.

For example I have text string (all in one cell)

text[5]: 24

text[6]: 89

text[7]: 83

and need to change 5, 6, 7 values (in brackets) to sorted values like 37, 38, 39 but keep all sintaxis as is. Only change numbers in brackets.

Do I need macros or it could be performed with tools built-in in Excel?

Answer 1

Hello Punkito2,

That is great to hear.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Asher

Answer 2

Hello ,

Thank you for posting your question in our community, what I would suggest is to create a new column and single out the numbers in the brackets. This could be done with a formula like:

=MID(B27,5,1)

You can adjust the formula depending on the amount of characters involved.

After that you can sort based on the new column which will contain only the numbers in the brackets.

Please let us know if you need more help.

Best Regards, Asher

Answer 3

Thank you for solution!

I have resolved my case with little different way - with left/right formula since the count of symbols from both sides are equal

=LEFT(A1,18)&B1&RIGHT(A1,22)