Formula to clean up data

[tesleen2025]

Hi, I need a formula to clean up the below text. I only want the numbers from each section code.
So for 393.60(d) I only want to see 393.60
I'm having trouble with this one since they vary so much from code to code.

Section

396.11

393.60(d)

393.201(c)

393.47(e)

393.9T

396.3(a)(1)

393.51

393.90

393.45B2PC

393.90

<tbody>
</tbody>

Thank you,
tesleen

 

[CyrusTheVirus]

Welcome to the forums. Try out the below formula, hope this works for you. It assumes that all cells have a decimal within the number.

Enter the formula with Ctrl+Shift+Enter, not just Enter.

B2:

=IF(ISNUMBER(A2),A2,VALUE(LEFT(A2,SMALL(IF(ISERROR(VALUE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))),ROW(INDIRECT("1:"&LEN(A2)))),2)-1)))

Section	Extraction
396.11	396.11
393.60(d)	393.6
393.201(c)	393.201
393.47(e)	393.47
393.9T	393.9
396.3(a)(1)	396.3
393.51	393.51
393.9	393.9
393.45B2PC	393.45
393.9	393.9

<tbody>
</tbody><colgroup><col><col></colgroup>

 

[Trustee]

Hi, I need a formula to clean up the below text. I only want the numbers from each section code.
So for 393.60(d) I only want to see 393.60
I'm having trouble with this one since they vary so much from code to code.

Section

396.11

393.60(d)

393.201(c)

393.47(e)

393.9T

396.3(a)(1)

393.51

393.90

393.45B2PC

393.90

<tbody>
</tbody>

Thank you,
tesleen

Please follow the steps to perform this feature:

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0,1),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))
Please let me know if you have any query.

 

[CyrusTheVirus]

Hi Trustee,

I do not think that your formula will give OP what they want. Your formula removes the decimals therefore changing the number to go from something like 393.60 to 39360... and it also includes ALL numbers, which could 'technically' be what OP wants, but probably not, for instance your formula makes 396.3(a)(1) turn into 39631.

Please advise.

 

[tesleen2025]

Cyrus,
This formula worked perfect. Thank you!

 

[CyrusTheVirus]

Good to hear. You're welcome.