Extract number only from “mix-up text & numbers data row”

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Using Excel 2000

Hi,

My range C11:Y29 of mix data, text & numbers from which I want to extract only numbers to finish my structure in the range AA11:AW29 as shown in the example attached.

Please need a formula but preferable if possible it is a VBA solution.

NX.xls
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW
1
2
3
4
5
6
7
8
9Find Last ColumnEM1234567891011121314151617181920212223
10Serial NumbersEM12345678910111213141516171819202122231234567891011121314151617181920212223
111N.X1N.XN.X2N.XN.XN.X1N.XN.XN.XN.X1N.X1N.XN.X12111
122
13
141N.X1N.XN.X3N.XN.X1N.X1N.XN.X3N.X1131131
156
16
172N.X1N.XN.X3N.X2N.X6N.X113261
188
19
2022N.X2N.XN.XN.XN.X2N.XN.X3N.X1N.X22231
2112
22
2352N.XN.X13N.X12131
2413
25
2662N.X14N.X12141
277
28
2982N.XN.X1N.XN.X1N.X3N.X1N.XN.XN.X2211312
3010
31
32
33
34
35
Test


Thank you in advance

Regards,
Kishan
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Try this:

VBA Code: 
Sub ExtractNumeber()
  Dim i&, j&, k&
  For i = 11 To 29
    k = Columns("AA").Column
    For j = 3 To 25
      If Cells(i, j) <> "N.X" And Cells(i, j) <> "" Then
        Cells(i, k) = Cells(i, j)
        k = k + 1
      End If
    Next
  Next
End Sub
-------------------------------

If you have 365, put the following formula in AA11, it automatically fills in to the right, then copies it down.

Excel Formula: 
=FILTER(C11:Y11,ISNUMBER(C11:Y11),"")

:cool:
 
Last edited:
Upvote 1
Solution
DanteAmor said:
Try this:

VBA Code: 
Sub ExtractNumeber()
  Dim i&, j&, k&
  For i = 11 To 29
    k = Columns("AA").Column
    For j = 3 To 25
      If Cells(i, j) <> "N.X" And Cells(i, j) <> "" Then
        Cells(i, k) = Cells(i, j)
        k = k + 1
      End If
    Next
  Next
End Sub
-------------------------------

If you have 365, put the following formula in AA11, it automatically fills in to the right, then copies it down.

Excel Formula: 
=FILTER(C11:Y11,ISNUMBER(C11:Y11),"")

:cool:
Click to expand...
Master DanteAmor, thank you very much for giving a complete solution of my queries consecutive 3rd day, all of your macros have worked for me like a magic with 100% accuracy quick adaptable in every changeable situations so perfectly. 🙌

I am still using version 2000 only. So far the formula does not worked. Thank you for the alternative answer also.

You all done for me beyond my expectations.

God Bless You, I wish Good Luck to you.

Kind Regards,
Kishan :)
 
Upvote 0
Kishan said:
Using Excel 2000
Click to expand...
I think this only uses functions available back then.

23 05 13.xlsm
CDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
10123456789101112131415161718192021222312345678
11N.X1N.XN.X2N.XN.XN.X1N.XN.XN.XN.X1N.X1N.XN.X12111   
12        
13        
14N.X1N.XN.X3N.XN.X1N.X1N.XN.X3N.X1131131  
15        
16        
17N.X1N.XN.X3N.X2N.X6N.X113261   
18        
19        
202N.X2N.XN.XN.XN.X2N.XN.X3N.X1N.X22231   
21        
22        
232N.XN.X13N.X12131     
24        
25        
262N.X14N.X12141     
27        
28        
292N.XN.X1N.XN.X1N.X3N.X1N.XN.XN.X2211312  
Numbers
Cell Formulas
RangeFormula
AA11:AH29AA11=IF(COLUMNS($AA:AA)>COUNT($C11:$Y11),"",INDEX($C11:$Y11,SMALL(IF(ISNUMBER($C11:$Y11),COLUMN($C11:$Y11)-COLUMN($C11)+1),COLUMNS($AA:AA))))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 1
Peter_SSs said:
I think this only uses functions available back then.

23 05 13.xlsm
CDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
10123456789101112131415161718192021222312345678
11N.X1N.XN.X2N.XN.XN.X1N.XN.XN.XN.X1N.X1N.XN.X12111   
12        
13        
14N.X1N.XN.X3N.XN.X1N.X1N.XN.X3N.X1131131  
15        
16        
17N.X1N.XN.X3N.X2N.X6N.X113261   
18        
19        
202N.X2N.XN.XN.XN.X2N.XN.X3N.X1N.X22231   
21        
22        
232N.XN.X13N.X12131     
24        
25        
262N.X14N.X12141     
27        
28        
292N.XN.X1N.XN.X1N.X3N.X1N.XN.XN.X2211312  
Numbers
Cell Formulas
RangeFormula
AA11:AH29AA11=IF(COLUMNS($AA:AA)>COUNT($C11:$Y11),"",INDEX($C11:$Y11,SMALL(IF(ISNUMBER($C11:$Y11),COLUMN($C11:$Y11)-COLUMN($C11)+1),COLUMNS($AA:AA))))
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...
Hello Peter_SSs , thank you for giving an option of formula yes it worked fine with 2000.

Have a good weekend, Good Luck!

Kind Regards,
Kishan
 
Upvote 0
You're welcome. Thanks for the confirmation. :)

Assuming the numerical values in columns C:Y are not the result of formulas (it appears that way from your XL2BB mini sheet) then another vba approach would be to copy all the numbers for a row at once.

VBA Code: 
Sub Numbers_Only()
  Dim r As Long
 
  On Error Resume Next
  For r = 11 To 29
    Intersect(Rows(r), Columns("C:Y")).SpecialCells(xlConstants, xlNumbers).Copy Range("AA" & r)
  Next r
End Sub
 
Upvote 1
Peter_SSs said:
You're welcome. Thanks for the confirmation. :)

Assuming the numerical values in columns C:Y are not the result of formulas (it appears that way from your XL2BB mini sheet) then another vba approach would be to copy all the numbers for a row at once.

VBA Code: 
Sub Numbers_Only()
  Dim r As Long
 
  On Error Resume Next
  For r = 11 To 29
    Intersect(Rows(r), Columns("C:Y")).SpecialCells(xlConstants, xlNumbers).Copy Range("AA" & r)
  Next r
End Sub
Click to expand...
Hello Peter_SSs, yes it is true the values are not the result of formulas; your code is shorter worked flawless giving the correct results. Thanks you for giving a choice.

Have a good weekend, Good Luck!

Kind Regards,
Kishan :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Extracting and Formatting Numbers from Data Rows
2
Replies
12
Views
316
TheWes
T
H
  • Question Question
data extract between sheets For the value entered
Replies
1
Views
328
LazyBug
LazyBug
C
  • Question Question
Extracting Specific Fields from multiple PDFs with VBA (Excel 2019)
Replies
1
Views
1K
chickyguy
C
E
  • Solved
Formula for extracting multiple occurrences of text substring in a cell
2
Replies
19
Views
888
Sektor
Sektor
K
  • Solved
Find lottery 5_50 3rd price combinations from 12 numbers
Replies
9
Views
848
Kishan
K

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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