extract multiple numbers from String

[ravi2628]

Hi everyone Good Morning/Afternoon/Evening/Night,

i was looking for a logic to extract the numbers from a string,the data is as follows.

Input:

100IU/1ML	10ML
100IU/1ML	3ML
100IU/1ML	3ML
100IU/1ML	10ML
500Y/1ML	1ML
1MG/1ML	10ML

Output:

100IU/1ML	10ML	100​	1​	10​
100IU/1ML	3ML	100​	1​	3​
100IU/1ML	3ML	100​	1​	3​
100IU/1ML	10ML	100​	1​	10​
500Y/1ML	1ML	500​	1​	1​
1MG/10ML	10ML	1​	10​	10​

Output required columns are 3,4 is derived from Column 1
Output required column 5 is derived from column 2

Please help me out.

Regards,
Raviteja

 

[DanteAmor]

If your texts always end in "ML", try this:

Dante Amor
	A	B	C	D	E
1
2	100IU/1ML	10ML	100	1	10
3	100IU/1ML	3ML	100	1	3
4	100IU/1ML	3ML	100	1	3
5	100IU/1ML	10ML	100	1	10
6	500Y/1ML	1ML	500	1	1
7	1MG/1ML	10ML	1	1	10
8	1000MG/10ML	5ML	1000	10	5
Hoja15

Cell Formulas
Range	Formula
C2:C8	C2	=MAX(IFERROR(MID(LEFT(A2,FIND("/",A2)-1),COLUMN($A$1:$J$1),ROW($A$1:$A$10))+0,0))
D2:D8	D2	=TRIM(SUBSTITUTE(MID(A2,FIND("/",A2)+1,LEN(A2)),"ML",""))+0
E2:E8	E2	=SUBSTITUTE(B2,"ML","")+0
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ravi2628]

If your texts always end in "ML", try this:

Dante Amor
	A	B	C	D	E
1
2	100IU/1ML	10ML	100	1	10
3	100IU/1ML	3ML	100	1	3
4	100IU/1ML	3ML	100	1	3
5	100IU/1ML	10ML	100	1	10
6	500Y/1ML	1ML	500	1	1
7	1MG/1ML	10ML	1	1	10
8	1000MG/10ML	5ML	1000	10	5
Hoja15

Cell Formulas
Range	Formula
C2:C8	C2	=MAX(IFERROR(MID(LEFT(A2,FIND("/",A2)-1),COLUMN($A$1:$J$1),ROW($A$1:$A$10))+0,0))
D2:D8	D2	=TRIM(SUBSTITUTE(MID(A2,FIND("/",A2)+1,LEN(A2)),"ML",""))+0
E2:E8	E2	=SUBSTITUTE(B2,"ML","")+0
Press CTRL+SHIFT+ENTER to enter array formulas.

Hi your logic is working but i have got an issue

Int-Strength	Int-Pack Vol
1.5MG/1ML	0.5ML	5	1	0.5

but the output need to be like this

Int-Strength	Int-Pack Vol
1.5MG/1ML	0.5ML	1.5	1	0.5

in column B i have remaining different Characters "ML"

0.4ML

500MG

1.1KG

1.2KG

2G

 

[Rick Rothstein]

Try these formulas in the indicated cells...

C2: =LOOKUP(9.9E+307,--LEFT(A2,ROW($1:$99)))

D2: =LOOKUP(9.9E+307,--LEFT(MID(A2,FIND("/",A2)+1,99),ROW($1:$99)))

E2: =LOOKUP(9.9E+307,--LEFT(B2,ROW($1:$99)))

 

[ravi2628]

Try these formulas in the indicated cells...

C2: =LOOKUP(9.9E+307,--LEFT(A2,ROW($1:$99)))

D2: =LOOKUP(9.9E+307,--LEFT(MID(A2,FIND("/",A2)+1,99),ROW($1:$99)))

E2: =LOOKUP(9.9E+307,--LEFT(B2,ROW($1:$99)))

Hi Rick,

thanks for the Logic it works very well.

i need some help for this data also

Input:

8MG+100MG+2MG

900MG/1ML+60MG/1ML

97.5MG/5ML+100MG/5ML+9MG/5ML

7.5Y+750Y+162MG

67.5MG/5ML+6.8MG/5ML

67.5MG/5ML+6.8MG/10ML

Output:

8MG+100MG+2MG	8		100		2
900MG/1ML+60MG/1ML	900	1	60	1
97.5MG/5ML+100MG/5ML+9MG/5ML	97.5	5	100	5	9	5
7.5Y+750Y+162MG	7.5		750		162
67.5MG/5ML+6.8MG/5ML	67.5	5	6.8	5
67.5MG/5ML+6.8MG/10ML	67.5	5	6.8	10

Can you please help me out

 

[DanteAmor]

8MG+100MG+2MG	8	100	2
900MG/1ML+60MG/1ML	900	1	60	1
97.5MG/5ML+100MG/5ML+9MG/5ML	97.5	5	100	5	9	5
7.5Y+750Y+162MG	7.5	750	162
67.5MG/5ML+6.8MG/5ML	67.5	5	6.8	5
67.5MG/5ML+6.8MG/10ML	67.5	5	6.8	10

Try the following macro.
Assuming your data starts in A2, results in B2 onwards

Sub extract_Numbers()
  Dim c As Range
  Dim i As Long, j As Long
  Dim b As Variant, m As Variant, d As Variant
  
  ReDim b(1 To Range("A" & Rows.Count).End(3).Row, 1 To 99)
  For Each c In Range("A2", Range("A" & Rows.Count).End(3))
    j = 0
    i = i + 1
    For Each m In Split(c, "+")
      For Each d In Split(m, "/")
        j = j + 1
        b(i, j) = Val(d)
      Next
      If UBound(Split(m, "/")) = 0 Then j = j + 1
    Next
  Next
  Range("B2").Resize(i, UBound(b, 2)).Value = b
End Sub

 

[Rick Rothstein]

This should do what you asked for in Message #5 spacing the numbers in the columns as you indicated you wanted...

Sub BreakApartData()
  Dim R As Long, X As Long, Data As Variant, Arr As Variant
  Data = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X, 1) Like "[!0-9/+]" Then Mid(Data(R, 1), X) = " "
    Next
    Data(R, 1) = Replace(Data(R, 1), " ", "")
    Arr = Split(Data(R, 1), "+")
    For X = 0 To UBound(Arr)
      If Not Arr(X) Like "*/*" Then Arr(X) = Arr(X) & "/"
    Next
    Data(R, 1) = Join(Arr, "/")
  Next
  Application.ScreenUpdating = False
  With Range("B2").Resize(UBound(Data))
    .Value = Data
    .TextToColumns , xlDelimited, , , False, False, False, False, True, "/"
  End With
  Application.ScreenUpdating = True
End Sub

 

[ravi2628]

Try the following macro.
Assuming your data starts in A2, results in B2 onwards

Sub extract_Numbers()
  Dim c As Range
  Dim i As Long, j As Long
  Dim b As Variant, m As Variant, d As Variant
 
  ReDim b(1 To Range("A" & Rows.Count).End(3).Row, 1 To 99)
  For Each c In Range("A2", Range("A" & Rows.Count).End(3))
    j = 0
    i = i + 1
    For Each m In Split(c, "+")
      For Each d In Split(m, "/")
        j = j + 1
        b(i, j) = Val(d)
      Next
      If UBound(Split(m, "/")) = 0 Then j = j + 1
    Next
  Next
  Range("B2").Resize(i, UBound(b, 2)).Value = b
End Sub

Thanks DanteAmor Your Logic Worked

 

[ravi2628]

This should do what you asked for in Message #5 spacing the numbers in the columns as you indicated you wanted...

Sub BreakApartData()
  Dim R As Long, X As Long, Data As Variant, Arr As Variant
  Data = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X, 1) Like "[!0-9/+]" Then Mid(Data(R, 1), X) = " "
    Next
    Data(R, 1) = Replace(Data(R, 1), " ", "")
    Arr = Split(Data(R, 1), "+")
    For X = 0 To UBound(Arr)
      If Not Arr(X) Like "*/*" Then Arr(X) = Arr(X) & "/"
    Next
    Data(R, 1) = Join(Arr, "/")
  Next
  Application.ScreenUpdating = False
  With Range("B2").Resize(UBound(Data))
    .Value = Data
    .TextToColumns , xlDelimited, , , False, False, False, False, True, "/"
  End With
  Application.ScreenUpdating = True
End Sub

Thanks Rick Your Logic Worked