Extract measurements from cell values using VBA

Dear Experts:

below are two sample columns. As you can see I would like to run a macro that extracts the measurements from the 'Product_Description' Column and paste them into the column to the right

Product_Description                                               Extracted_Measurements
Coronary scissors, 125°, 10mm, rd, 21cm                       125°, 10mm, rd, 21cm
Coronary sciss., 125°, 10mm, rd, 23cm                               125°, 10mm, rd, 23cm
Coronary sciss., 145°, 10mm, rd, 23cm                                145°, 10mm, rd, 23cm
Coronary sciss., 160°, 10mm, rd, 23cm                                160°, 10mm, rd, 23cm
Coronary sciss., 190°, 10mm, rd, 23cm                                190°, 10mm, rd, 23cm
Coronary sciss., 125°, 10mm, rd, 23cm                                125°, 10mm, rd, 23cm
SCISSORS, STRULLY, BL/BL, CVD., 22 CM                                 22 CM
Scissors, Strully, pd/pd, cvd., 22 cm                                 22 cm
DURA SCISSORS, SCHMIDEN-TAYLOR, 16.5 CM                 16.5 CM
SCISSORS, TOENNIS-ADSON, CVD., 17.5 CM                        17.5 CM
Scissors, Toennis, str., 18 cm                                                18 cm
Scissors, Toennis, cvd., 18 cm                                        18 cm
SCISSORS, OLIVECRONA, KNEE BENT, 13 CM                        13 CM
Cartilage scissors, McIndoe, 18.5 cm                                18.5 cm


The macro is to loop thru Column 'Product Description' with the following requirement      
... move to the first number in the cell value and copy from that point till the end of the cell and paste the contents of the clipboard into the cell to the right.
(see example above) .

I have attached a sample file for your convenience.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Extracting-Measurements.xlsx
Andreas HermleTeam leaderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi Andreas,

Please enter this array formula into cell [D2]:
=MID(C2,FIND(MATCH(TRUE,ISNUMBER(1*MID(C2,FIND(ROW($1:$9),C2),1)),0),C2),255)

An array formula is entered by using [CTRL]+[SHIFT]+[ENTER] instead of just [ENTER].

Then copy cell [D2] to the Windows Clipboard & copy down column [D] until row 15.

I have updated your workbook.  Please see the attachment to this comment.

BFN,

fp.
Q-28247933.xlsx
0
KimputerCommented:
Just in case you don't want to work with formulas (because the lenght of the column is different every time), and prefer vba:

Sub test()

 Range("C2").Select
 Do Until ActiveCell.Value = ""
    temp = ActiveCell.Value
    temp = Split(temp, ",")
    temp2 = ""
    For i = 0 To UBound(temp)
        If Asc(Left(temp(i), 1)) = 32 Then
            temp(i) = Right(temp(i), (Len(temp(i)) - 1))
        End If
    Next
    found = -1
    For i = 0 To UBound(temp)
        If Asc(Left(temp(i), 1)) > 47 And Asc(Left(temp(i), 1)) < 58 Then
        found = i
        Exit For
    End If
    
    Next
    
    If found > -1 Then
        For i = found To UBound(temp)
            If temp2 = "" Then
                temp2 = temp(i)
            Else
                temp2 = temp2 + ", " + temp(i)
            End If
        Next
    End If
    
   ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = temp2
  ActiveCell.Offset(1, -1).Select
 Loop

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:
Just in case you don't want to work with formulas (because the lenght of the column is different every time), and prefer vba:

PS. Just the "255" in the formula needs to change to accommodate longer values (if needed).
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Andreas HermleTeam leaderAuthor Commented:
Dear both,

thank you very much for your swift support. I will do some testing later today and then let you know. Again, thank you very much for your professional support.

Regards, Andreas
0
[ fanpages ]IT Services ConsultantCommented:
You're very welcome, Andreas.

There is no rush to respond, if you are busy today.

(It will give barryhoudini chance to read this question & improve upon my formula!)
0
Andreas HermleTeam leaderAuthor Commented:
Dear both,

was able to test it earlier. Wow, I am truly impressed!! Both approaches work just fine.

Thank you very much for your super fast and highly professional support. I really appreciate it.

Regards, Andreas
0
Andreas HermleTeam leaderAuthor Commented:
Great job!
0
[ fanpages ]IT Services ConsultantCommented:
Thanks for closing the question so promptly, Andreas.

Good luck with your project.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.