Replace string with vba

[diyVBA]

I have strings which vary like:

1. 12 X 14 SCALE PAPER 21#(5 X1M/CS) 9927

2. 12 X 18 DRY WAX21LB (1M/BOX)


3. 12 X 14 DRY WAX SCALE 18LB (1M/BDL/5 BDL/CS) J


I have to do maths on these which will be like this:

1. 12*14*21
2. 12*18*21
3. 12*14*18

I am having a hard time using REPLACE/SUBSTITUTE to solve this, because the spaces vary between different cells.

Suggestions?

[diyVBA]

There is no underline in the actual string.
The string always starts with the number and the third number always has # or LB at the end of the number which I want to use.

[PCI]

assuming data in column "A"
see next code

Please Login or Register  to view this content.

[diyVBA]

This is great, but its kinda complicated for me to understand to modify for my need (I would want the fixes to be done in some other column and not column A itself, i.e. after the code works I want column lets say G to have 12*14*21 not in column A itself).

Also for the below string:

12 X 14 SCALE PAPER 21#(5 X1M/CS) 9927

this is what it does:

12. 14x21x9927

[diyVBA]

It also gives a subscript out of range error and stops after row 86

[PCI]

Please attach a short Excel sample

[diyVBA]

Excel sample.

[PCI]

The file was really welcome ...!
Ajust DstCol to your need
Pay attention to row 926

Please Login or Register  to view this content.

[diyVBA]

works just perfectly!!

[PCI]

Did you double check row 926 ???

[diyVBA]

yes I mean other than that, these are exceptions which will happen in the dataset (2-3 times)

[diyVBA]

Lets say the string is 15 X 18 DRY WAX SCALE 21LB (2000/BOX)

It would be better if I had 15 in one column, 18 in another column and 21 in another column for me to then multiply it

I have to multiply 15x18x21

[PCI]

yes

Good news
Enjoy Excel

[jindon]

It would be better if I had 15 in one column, 18 in another column and 21 in another column for me to then multiply it

I have to multiply 15x18x21

Here's how I read your problem.
Extract numbers:
1) From the beginning
2) preceded by an X
3) followed by LB or #
So, for example
row 87
12 X 18 SCALE PAPER (17 LBS/BDL)
The code extracts 12, 18 & 17
If any problem, post back with your desired result.

Please Login or Register  to view this content.

[NhatChiLan]

You refer to try
I am learning VBA. Therefore, Code has many problems. Please ignore it

Function My_Product(ByVal Text As String, Choise As Boolean) 
    Dim i As Integer, n As Long 
    Dim strTemp As String, Str, eText As String 
Text = Split(Text, "(")(0) 
Text = Replace(Text, "#", "") 
Text = Replace(Text, "X", " ") 
Text = Replace(Text, " ", "*") 
Text = Replace(Text, "/", "*") 
For i = 1 To Len(Text) 
    Select Case Asc(Mid(Text, i, 1)) 
        Case 40 To 57, 94 
            strTemp = strTemp & Mid(Text, i, 1) 
    End Select 
Next i 
For Each Str In Split(strTemp, "*") 
    If Str <> "" Then 
        If n = 3 Then Exit For 
        If Not IsNumeric(Str) Then 
            Str = Val(StrReverse(Str)):   Str = StrReverse(Val(Str)) 
            If Str = 0 Then GoTo 1 
        End If 
        eText = IIf(eText = "", Str, eText & "*" & Str) 
        n = n + 1 
    End If 
1: 
Next 
If n > 2 Then 
    If Choise = False Then My_Product = eText Else My_Product = Evaluate(eText) 
End If 
End Function  

[sktneer]

You may give this a try to see if this covers all your strings and find the measurements correctly.
In the attached click the button called "Find Measurements & Calculate" to run the code.

Please Login or Register  to view this content.

[PCI]

It would be better if I had 15 in one column, 18 in another column and 21 in another column for me to then multiply it

I have to multiply 15x18x21

Do you want 3 columns with value or final result ?
How will be the display for row 926 ??

[ikboy]

try

Please Login or Register  to view this content.

[diyVBA]

Hi Guys,

I have a string which also looks like this:

12 X 12 SCALE PAPER (10 LBS/BOX))

I need to use the numbers next to # LB and LBS, sorry this wasn't stated by me in the original requirement.
12*12*10

[jindon]

12 X 12 SCALE PAPER (10 LBS/BOX))

I need to use the numbers next to # LB and LBS, sorry this wasn't stated by me in the original requirement.
12*12*10

Have you tried my code in
https://www.excelforum.com/excel-pro...ml#post5296324
?

[sktneer]

It seems you are not testing all the proposed codes?

[diyVBA]

I have just come to work, I will test all proposed solution. Thanks.

[diyVBA]

Sorry, testing it now.

[diyVBA]

I just tested your code and seems to do what I wanted. This is perfect.

[diyVBA]

jindon,

Suppose my column is now in F instead of A and I want the results to be pasted from Column Q, so Q, R and S.
How do I modify your code?
I am a beginner with VBA, so everything is complex for me.

[jindon]

Try change to

Please Login or Register  to view this content.

[diyVBA]

jindon,

This is just perfect, thanks!!
If I want to divide the product with 432, can I do this -- >

=product(rc[-3]:rc[-1])/(432)

[jindon]

You can just change that line to

Please Login or Register  to view this content.

Please do not quote full post, the forum doesn't like it...-

[diyVBA]

Ok, thanks! It works.

[diyVBA]

Do you want 3 columns with value or final result ?
How will be the display for row 926 ??

926 is an exception.

[jeffreybrown]

Hi diyVB,

Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

[diyVBA]

Noted, thanks!

[diyVBA]

@jindon

I have the same requirement but for a different set of strings, I need to pick the numbers between 2 Xs and the string has HOT sometimes and sometimes it doesn't, but prime need is to pick the numbers before and after the Xs:

Strings can be like:

10.5X2X4.5 WIND.BAG PRT.2COL .WIND.1/2 FRONT/1/2 GOSSET (1M)
11 X 3 X 16 PIZZA BAGS PRINTED (500/B0X)
11X4X16 BROWN BREAD BAG PRINTED (1000/BOX)
340113-4.5" X 1.88" X 16.625" SUB PLAIN BAG (1000/BOX)
4 3/4 X 3 X 10 BAGS GP PRT. AROUCH V BOTTOM (1000/BOX)
5 1/4 X 3 1/2 X 12 BAR-B-Q BAGS (500/B0X
5.75X13.5X4.5 BROWN WAX BAG PRT.MEUNERIE URBAINE(1M/BOX0
6X3/4X63/4 SAC SANDWICH CIRE (1M/BOX)
8.75"X 2"X11" WHITE PIZZA BAG PRT. (1M)
7.5" X 2" X 8" FOIL HAMBURGER BAG (1000/BOX)
FOIL HAMBURGER BAGS 6X3/4X6.5 (1M/BOX)
FOIL HOT DOG 7X1.5X5.5 (1000/BOX)
FOIL HOT DOG BAG 7X1.5X5.5 (1000/BOX)
MANCHON HOT DOG GENERIQUE 3.5X1,25X7,5(1000/BOX)
POLYBAGS .002; 9x12


Seperate the numbers betweens the Xs and put in a different columns and then use formaula =product(rc[-3]:rc[-1]/432)

10.5X2X4.5 WIND.BAG PRT.2COL .WIND.1/2 FRONT/1/2 GOSSET (1M) ---> ((10.5*2)*(2*2)*(4.5*2))/432

11 X 3 X 16 PIZZA BAGS PRINTED (500/B0X) ---> ((11*2)*(3*2)*(16*2))/432

11X4X16 BROWN BREAD BAG PRINTED (1000/BOX) ---> ((11*2)*(4*2)*(16*2))/432

340113-4.5" X 1.88" X 16.625" SUB PLAIN BAG (1000/BOX) ---> ((4.5*2)*(1.88*2)*(16.625*2))/432

4 3/4 X 3 X 10 BAGS GP PRT. AROUCH V BOTTOM (1000/BOX) ---> ((4.75*2)*(3*2)*(10*2))/432

5 1/4 X 3 1/2 X 12 BAR-B-Q BAGS (500/B0X ---> ((5.25*2)*(3.5*2)*(12*2))/432

5.75X13.5X4.5 BROWN WAX BAG PRT.MEUNERIE URBAINE(1M/BOX0 ---> ((5.75*2)*(13.5*2)*(4.5*2))/432

6X3/4X63/4 SAC SANDWICH CIRE (1M/BOX) ---> ((6*2)*(0.75*2)*(6.75*2))/432

8.75"X 2"X11" WHITE PIZZA BAG PRT. (1M) ---> ((8.75*2)*(2*2)*(11*2))/432

7.5" X 2" X 8" FOIL HAMBURGER BAG (1000/BOX) ---> ((7.5*2)*(2*2)*(8*2))/432

FOIL HAMBURGER BAGS 6X3/4X6.5 (1M/BOX) ---> ((6*2)*(0.75*2)*(6.5*2))/432

FOIL HOT DOG 7X1.5X5.5 (1000/BOX) ---> ((7*2)*(1.5*2)*(5.5*2))/432

FOIL HOT DOG BAG 7X1.5X5.5 (1000/BOX) ---> ((7*2)*(1.5*2)*(5.5*2))/432

MANCHON HOT DOG GENERIQUE 3.5X1,25X7,5(1000/BOX) ---> ((3.5*2)*(1.25*2)*(7.5*2))/432

POLYBAGS .002; 9x12 ---> ((0.002*2)*(9*2)*(12*2))/432

These are all the different types.

[diyVBA]

How do I store the values of detected pattern into variables?