Cell will number and text

[outlawspeeder]

I looking for a VBA way to pull and replace cells that have both text and numbers. I nee to do math with the numbers and put it back in to the cell. Not all the Cells have Text. The ones without text, there is no need for math.

123 xyz ----->123*2--------->246
234----------------------------->234
2-------------------------------->2
24xyz---------->24*2---------->28
8809 abc----->8809*2------->17618

Thanks

 

[lrobbo314]

Are the letters following the numbers always 3 characters long? On 1 example you have a number followed by the letters with no space, but the other 2 are separated by a space. Do you have them both ways?

 

[sandy666]

with Power Query

Raw		Result
123 xyz		246
234		234
2		2
24xyz		48
8809 abc		17618

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Text = Table.AddColumn(Table.TransformColumnTypes(Source,{{"Raw", type text}}), "Text", each Text.Select([Raw],{"0".."9"})),
    TypeNumber = Table.TransformColumnTypes(Table.AddColumn(Text, "Number", each Text.Select([Raw],{"0".."9"})),{{"Number", Int64.Type}}),
    Result = Table.AddColumn(TypeNumber, "Result", each if [Raw] = [Text] then [Number] else [Number]*2),
    TSC = Table.SelectColumns(Result,{"Result"})
in
    TSC

 

[Rick Rothstein]

Is the rule to multiply any number followed by text times two (always two)?

 

[outlawspeeder]

It will always be the same number (not 2) The number is 28. I used 2 only for the sample.

"Rich BB Code" I can follow it but I don't understand it.


Thanks

 

[sandy666]

"Rich BB Code" I can follow it but I don't understand it.

write to whom you address the answer

if you are talking about post#3 this is M code for Power Query
btw update your profile about Excel version

 

[Rick Rothstein]

Give this macro a try (change the range to meet your actual needs)...

Sub Test()
  Dim Cell As Range
  Application.ScreenUpdating = False
  For Each Cell In Range("A1:A6")
    If Cell.Value Like "*[!0-9]*" Then
      Cell.Value = 28 * Val(Cell.Value)
    End If
  Next
  Application.ScreenUpdating = True
End Sub

 

[outlawspeeder]

Rick: It will always be the same number (not 2) The number is 28. I used 2 only for the sample.

Sandy: "Rich BB Code" I can follow it but I don't understand it. And if Power Query is an add in, I using a lock down computer, software, addins… Well I got VB but only if I save the file as a .xls (2003)


I can not update what ver here at work due to the check box being images are blocked. Vers I use 2019, 365, 2016, 2010 and file types for 2003 running 2016.. My life is fun

 

[sandy666]

2019, 365, 2016

so you have Power Query build-in

2010

this (and 2013) require Power Query add-in
the rest doesn't support PQ

Well I got VB but only if I save the file as a .xls (2003)

why? you can use .xlsm extension

 

[outlawspeeder]

Rick
If Cell.Value Like "*[!0-9]*" Then

"*[!0-9]*" What is this?

The rest I follow