How to insert formula to show value and also account for instances of 2 digits versus 3 digits.

[Burrgogi]

Sub Fanatical_Table()
'
    Workbooks.Open Filename:= _
        "D:\Games\Game Collection\Fanatical Bundle Tracker Workbook  (started on Nov 6, 2020).xlsm"
    Sheets.Add After:=Sheets(Sheets.Count), Type:= _
        "D:\Games\Fanatical Bundle Template 2C.xltx"
    ActiveSheet.Name = Format(Date, "mm_dd_yyyy")
    Range("A2").Select
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
    ' This deletes the last 2 unnecessary columns
    Columns("J:K").Select
    Selection.Delete Shift:=xlToLeft
    ' Cut the trading cards (TC) column and move it to the left of the Playmode col.
    Columns("I:I").Select
    Selection.Cut
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight
Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = "=MID(C2, 7, 2)"
End Sub

I've got a very simple VBA routine and I'm stumped on the last part. Column C contains a string like this:
Steam 100%
Steam 84%
Steam 95%
Steam 91%

As you see from my code, it looks at the 2 numbers after "Steam " and enters the number into col. E as a formula. Most of the time, my data has only 2 digits in that particular column, but there are some occasions when it contains 3 numbers. How can I modify my formula to account for those instances?

Also, I'd like col. E to display the value instead of the actual formula.

 

[Burrgogi]

I figured out the first part of my question. For anyone interested, this will extract whatever appears between the space and percentage sign.

=MID(C2,SEARCH(" ", C2)+1,SEARCH("%",C2,SEARCH("%", C2)-1)-SEARCH(" ", C2)-1)

 

[JoeMo]

Untested -try (on a copy of your worksheet) replacing the line just before the End Sub line with this:

With Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row)
    .Formula = "=MID(C2,SEARCH("" "", C2)+1,SEARCH(""%"",C2,SEARCH(""%"", C2)-1)-SEARCH("" "", C2)-1)+0"
    .Value = .Value
End With

I assumed you want the output as numbers not text. If that's wrong just remove the +0 from the end of the formula.

 

[Burrgogi]

Untested -try (on a copy of your worksheet) replacing the line just before the End Sub line with this:

With Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row)
    .Formula = "=MID(C2,SEARCH("" "", C2)+1,SEARCH(""%"",C2,SEARCH(""%"", C2)-1)-SEARCH("" "", C2)-1)+0"
    .Value = .Value
End With

I assumed you want the output as numbers not text. If that's wrong just remove the +0 from the end of the formula.

Thanks JoeMo, that works just fine. Small little problem though... the numbers appear like this:
9500%
8600%
7900%

I'm assuming that this is due to the fact that the column has been preformatted as percentage. I can edit my template but I was wondering if there's a way to fix that so it appears like this instead:
95%
86%
79%

 

[JoeMo]

Thanks JoeMo, that works just fine. Small little problem though... the numbers appear like this:
9500%
8600%
7900%

I'm assuming that this is due to the fact that the column has been preformatted as percentage. I can edit my template but I was wondering if there's a way to fix that so it appears like this instead:
95%
86%
79%

If you've formatted the column as a percent then replace the .formula line with this:

.Formula = "=(MID(C2,SEARCH("" "", C2)+1,SEARCH(""%"",C2,SEARCH(""%"", C2)-1)-SEARCH("" "", C2)-1)+0)/100"

 

[Burrgogi]

First I have a VBA question for you. Before you came into thread, I wasn't getting much of a response so I searched hard for an answer. I found a solution very similar to the one you posted in post #3.
In your answer, you have
.Value = .Value

In the solution I found on my own, I have
.Formula = .Value

They both produced the same result. How?

If you've formatted the column as a percent then replace the .formula line with this:

.Formula = "=(MID(C2,SEARCH("" "", C2)+1,SEARCH(""%"",C2,SEARCH(""%"", C2)-1)-SEARCH("" "", C2)-1)+0)/100"

Works like a charm, I appreciate the help JoeMo. Since you've been so helpful, I have 2 small other problems I need to fix.

1) Sometimes I have the workbook open and forget that's already open and when I try to run the macro, it throws up an error.
2) Sometimes there are 2 Fanatical bundle deals in one day so while the macro correctly inserts a new worksheet based on my template & names the worksheet as the current date, I run into a small issue later int he day when I run the macro for the 2nd deal I've found. I have no problem renaming the 1st sheet, but it would be nice to have this fully automated.
Maybe something like 9_21_2022 (A) for the first sheet and 9_21_2022 (B) for the second sheet when I run it a second time.

 

[JoeMo]

First I have a VBA question for you. Before you came into thread, I wasn't getting much of a response so I searched hard for an answer. I found a solution very similar to the one you posted in post #3.
In your answer, you have
.Value = .Value

In the solution I found on my own, I have
.Formula = .Value

They both produced the same result. How?



Works like a charm, I appreciate the help JoeMo. Since you've been so helpful, I have 2 small other problems I need to fix.

1) Sometimes I have the workbook open and forget that's already open and when I try to run the macro, it throws up an error.
2) Sometimes there are 2 Fanatical bundle deals in one day so while the macro correctly inserts a new worksheet based on my template & names the worksheet as the current date, I run into a small issue later int he day when I run the macro for the 2nd deal I've found. I have no problem renaming the 1st sheet, but it would be nice to have this fully automated.
Maybe something like 9_21_2022 (A) for the first sheet and 9_21_2022 (B) for the second sheet when I run it a second time.

You are welcome.
.Formula = .Value simply sets the cell formula to the value that formula returns, and the formula is converted to that value so there is no longer a formula in the cell.

I recommend starting a new thread to address your two small problems.