Problem in extracting numbers from text

Shweta

Shweta

Well-known Member
Joined
Jun 5, 2011
Messages
514
Hi All,

My data is:

Shweta(31056)
Anju(31004)
Pooja(31057)
Rahul(31075)
Gurdeep(31019)
Ankit(31038)
Neeleder(31029)
Roshan(31040)

I want Numbers only in next column like:


31056
31004
31057
31075
31019
31038
31029
31040

I am using the formula:

=MID(F14,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},F14&"0,1,2,3,4,5,6,7,8,9")),LEN(F14))

But output is coming like this:


31056)
31004)
31057)
31075)
31019)
31038)
31029)
31040)

I need to remove last bracket as well.

Can someone please tell me what changes I need to do in my formula.

Thanks,
Shweta
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
You could try this UDF...

Code: 
Function TextNum(ByVal txt As String, ByVal ref As Boolean) As String
' jindon
' http://www.mrexcel.com/forum/showthread.php?t=362461
' =TextNum(A1,1)
' 1 for Text only
' 0 for Numbers only
    With CreateObject("VBScript.RegExp")
        .Pattern = IIf(ref = True, "\d+", "\D+")
        .Global = True
        TextNum = .Replace(txt, "")
    End With
End Function
 
Upvote 0
This is for Excel.

Place the code in your workbook in a normal module

http://www.rondebruin.nl/code.htm

then in the spreadsheet you would use

=TextNum(F14,0)

<TABLE style="WIDTH: 142pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=189><COLGROUP><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 3744" width=117><COL style="WIDTH: 54pt" width=72><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 88pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21 width=117>Shweta(31056)

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=72>31056</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Anju(31004)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>31004</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Pooja(31057)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>31057</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Rahul(31075)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>31075</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Gurdeep(31019)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>31019</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Ankit(31038)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>31038</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Neeleder(31029)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>31029</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Roshan(31040)

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2843881 class=xl64>31040</TD></TR></TBODY></TABLE>
 
Upvote 0
Shweta said:
Hi All,

My data is:

Shweta(31056)
Anju(31004)
Pooja(31057)
Rahul(31075)
Gurdeep(31019)
Ankit(31038)
Neeleder(31029)
Roshan(31040)

I want Numbers only in next column like:


31056
31004
31057
31075
31019
31038
31029
31040

I am using the formula:

=MID(F14,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},F14&"0,1,2,3,4,5,6,7,8,9")),LEN(F14))

But output is coming like this:


31056)
31004)
31057)
31075)
31019)
31038)
31029)
31040)

I need to remove last bracket as well.

Can someone please tell me what changes I need to do in my formula.

Thanks,
Shweta
Click to expand...
Is the numeric portion ALWAYS 5 digits?
 
Upvote 0
Shweta said:
Hi All,

My data is:

Shweta(31056)
Anju(31004)
Pooja(31057)
Rahul(31075)
Gurdeep(31019)
Ankit(31038)
Neeleder(31029)
Roshan(31040)

output is coming like this:

31056)
31004)
31057)
31075)
31019)
31038)
31029)
31040)

I need to remove last bracket as well.
Click to expand...
Try this...

=SUBSTITUTE(MID(F14,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},F14&"0,1,2,3,4,5,6,7,8,9")),LEN(F14)),")","")
 
Upvote 0
Thanks All!

Rick's Formula is working fine.

To answer T.Valko's question yes, it's always of 5 digits.

Shweta
 
Upvote 0
Shweta said:
yes, it's always of 5 digits.
Click to expand...
Given the above answer, and the appearance of your original sample data, perhaps this would do the job for you?

=-RIGHT(F14,7)
 
Last edited:
Upvote 0
And also:
=MID(F14,SEARCH("(",F14,1)+1,5)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Extract number from cell containing text
Replies
4
Views
773
etaf
etaf
2BLUE4U
  • Solved
Extracting Numbers from Text String
2
Replies
10
Views
2K
2BLUE4U
2BLUE4U
J
  • Question Question
Extract Just Dates From Text String
2
Replies
11
Views
2K
DanteAmor
DanteAmor
O
  • Question Question
Extracting dates in any format from text cells while containing other numbers
Replies
9
Views
1K
gaz_chops
G
N
  • Question Question
Extract Numbers from string
2
Replies
14
Views
1K
Rick Rothstein
Rick Rothstein

Forum statistics

Threads
1,224,527
Messages
6,179,331
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top