How to split number from this text seperately using formual

[Learn Excel in Tamil]

Hi Team,

Please help me to split the number seperately from this text "Project ABC12Limited (9874) - Test ok"

I have this text in A1, i want 9874 only in A2, i used mid function but if

MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),4) but i am unable to get the exact output,

Please help me on the same

Thanks!

 

[Peter_SSs]

One example isn't much to go on.
If the number is always in parentheses & there is only one set of parentheses in the text, then try

=REPLACE(LEFT(A1,FIND(")",A1)-1),1,FIND("(",A1),"")+0

Otherwise, post more varied examples and the expected results along with any more explanation that you can give.

 

[Gerald Higgins]

Hi, will the number ALWAYS be enclosed in brackets, AND be the ONLY part of the string in brackets ?

If you answer YES to both parts, then try this

=MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)

And if you want this to be treated as a number, not a text string, try this

=MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)+0

 

[Learn Excel in Tamil]

Hi Gerald Higgins,

Many thanks for your feedback

This formula working "=MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)" for the this content "Project ABC12Limited (9874) - Test ok"

But few <g class="gr_ gr_137 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" id="137" data-gr-id="137">i</g> have content like "Project ABC12Limited (<g class="gr_ gr_213 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="213" data-gr-id="213">Code :</g>9874) - <g class="gr_ gr_173 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="173" data-gr-id="173">Test ok</g>" those case it reflecting with <g class="gr_ gr_281 gr-alert gr_gramm gr_inline_cards gr_run_anim Style multiReplace" id="281" data-gr-id="281">Code :</g>9874, please help in such case <g class="gr_ gr_513 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" id="513" data-gr-id="513">please</g>

Thanks!

 

[Learn Excel in Tamil]

Hi Gerald,

Many thanks it's working but few text content with in the bracket insted of only number
Hence i add some more funcition on formual now it's working fine "RIGHT(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1),(LEN(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1))-MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)&"01234567890")))+1)"

Sample text
"Project ABC12Limited (9874) - Test ok"
"Project ABC12Limited (Code : 9874) - Test ok"

working fine for both, Many thanks again

 

[Peter_SSs]

Hence i add some more funcition on formual now it's working fine "RIGHT(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1),(LEN(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1))-MIN(FIND({0,1,2,3,4,5,6,7,8,9},MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)&"01234567890")))+1)"

Sample text
"Project ABC12Limited (9874) - Test ok"
"Project ABC12Limited (Code : 9874) - Test ok"

Here is a much shorter one that still works for those two example formats.

Excel Workbook
	A	B
2	Project ABC12Limited (9874) - Test ok	9874
3	Project ABC12Limited (Code : 9874) - Test ok	9874
Extract Number

 

[Learn Excel in Tamil]

Thanks Peter_SSs

 

[Peter_SSs]

Thanks Peter_SSs

You are welcome.