Covert Data into Text with VBA Code

An Quala

An Quala

Board Regular
Joined
Mar 21, 2022
Messages
146
Office Version
  1. 2021
Platform
  1. Windows
Hello, so when I download this sheet from the web, it always shows this error "Convert to number", basically in few of columns (where there are numbers) it is stored as different format which does not allow you to sort with number and mathematical operations, I change it manually with text-to-column option or by directly clicking on error message and select "Number stored as text" and it works fine,

Now I want a VBA Code which can do it,

Kindly note that there are only few columns which are having this error for example T, AA, AB, AC. AD, AE, etc so you can just do for these columns or full sheet as well, both will work fine as other data won't get affected,

EDIT: Somehow here in XL2BB, it already has changed the format into numbers apparently, but you would understand what problem I am facing, I am also attaching a screenshot of the problem.

Thank you in advance.


Deleting Rows Macro.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
1ProductEntityOperationCampaign IdAd Group IdPortfolio IdAd Id (Read only)Keyword Id (Read only)Product Targeting Id (Read only)Campaign NameAd Group NameStart DateEnd DateTargeting TypeStateDaily BudgetSKUASINAd Group Default BidBidKeyword TextMatch TypeBidding StrategyPlacementPercentageProduct Targeting ExpressionImpressionsClicksClick-through RateSpendSalesOrdersUnitsConversion RateAcosCPCROASCampaign Name (Informational only)Ad Group Name (Informational only)Campaign State (Informational only)Ad Group State (Informational only)Ad Group Default Bid (Informational only)Resolved Product Targeting Expression (Informational only)
2Sponsored ProductsKeyword51810182230962103843358501771133091597391670enabled0.58dfgbroad000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.4
3Sponsored ProductsKeyword5181018223096210384335850177120484689160127enabled0.64dfgbroad2085100.48%5.00.0000.00.0%0.50.0asdasdenabledenabled0.4
4Sponsored ProductsKeyword51810182230962103843358501771147161619594891enabled0.58dfgbroad2300.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.4
5Sponsored ProductsKeyword51810182230962103843358501771117074780596747enabled0.62dfgbroad39400.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.4
6Sponsored ProductsKeyword51810182230962103843358501771198054310782002enabled0.64dfgbroad000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.4
7Sponsored ProductsKeyword51810182230962103843358501771216796694767871enabled0.64dfgbroad23100.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.4
8Sponsored ProductsKeyword51810182230962103843358501771116800394856451enabled0.64dfgbroad600.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.4
9Sponsored ProductsKeyword22465486782470018626710936833683911309498353enabled1.08dfgexact000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled1.1
10Sponsored ProductsKeyword22465486782470018626710936833653354652434783enabled1.08dfgexact000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled1.1
11Sponsored ProductsKeyword2246548678247001862671093683362712747284010enabled0.97dfgexact3400.0%0.00.0000.00.0%0.00.0asdasdenabledenabled1.1
12Sponsored ProductsKeyword2246548678247001862671093683368880509831632enabled0.97dfgexact200.0%0.00.0000.00.0%0.00.0asdasdenabledenabled1.1
13Sponsored ProductsKeyword22465486782470018626710936833629871655919998enabled1.08dfgexact100.0%0.00.0000.00.0%0.00.0asdasdenabledenabled1.1
14Sponsored ProductsKeyword224654867824700186267109368336207922361834615enabled1.57dfgexact1000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled1.1
15Sponsored ProductsKeyword22465486782470018626710936833682552476516641enabled0.8dfgexact000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled1.1
16Sponsored ProductsKeyword224654867824700186267109368336125041523559069enabled1.08dfgexact000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled1.1
17Sponsored ProductsKeyword224654867824700186267109368336178224892278959enabled1.17dfgexact000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled1.1
18Sponsored ProductsKeyword224654867824700186267109368336237702370532591enabled1.25dfgexact100.0%0.00.0000.00.0%0.00.0asdasdenabledenabled1.1
19Sponsored ProductsKeyword60228050322464225369445700841232179385161330enabled0.11dfgphrase100.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.06
20Sponsored ProductsKeyword60228050322464225369445700841255771627511436enabled0.12dfgphrase000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.06
21Sponsored ProductsKeyword60228050322464225369445700841176928454042161enabled0.12dfgphrase000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.06
22Sponsored ProductsKeyword6022805032246422536944570084181841757906585enabled0.12dfgphrase000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.06
23Sponsored ProductsKeyword6022805032246422536944570084180654821572179enabled0.12dfgphrase000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.06
24Sponsored ProductsKeyword60228050322464225369445700841273551451104575enabled0.12dfgphrase000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.06
25Sponsored ProductsKeyword6022805032246422536944570084140001743584860enabled0.12dfgphrase000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.06
26Sponsored ProductsKeyword60228050322464225369445700841259424722839019enabled0.12dfgphrase000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.06
27Sponsored ProductsKeyword60228050322464225369445700841278118504947134enabled0.12dfgphrase000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.06
28Sponsored ProductsKeyword6022805032246422536944570084198753070646170enabled0.12dfgphrase000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.06
29Sponsored ProductsProduct Targeting103977923004034221037315953408186561934932770enabled0.48dfgcategory="6292252011"57610.17%0.370.0000.00.0%0.370.0asdasdenabledenabled0.4sfdsa
30Sponsored ProductsKeyword23189768308182564716315089601107190882986enabled1.08dfgexact000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.35
31Sponsored ProductsKeyword23189768308182564716315089601250229049391726enabled1.08dfgexact000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.35
32Sponsored ProductsKeyword23189768308182564716315089601159629857812834enabled0.47dfgexact000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.35
33Sponsored ProductsKeyword2318976830818256471631508960199621606761191enabled0.97dfgexact1900.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.35
34Sponsored ProductsKeyword23189768308182564716315089601262282056477120enabled1.08dfgexact000.0%0.00.0000.00.0%0.00.0asdasdenabledenabled0.35
Sheet2
 

Attachments

  • Text Format Error.png
    Text Format Error.png
    41.1 KB · Views: 8
  • Warning Message.png
    Warning Message.png
    44.6 KB · Views: 10

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
@An Quala
Try this:
VBA Code: 
Sub toNumber()
Dim c As Range
    Set c = ActiveSheet.UsedRange
    va = c.Value
    ActiveSheet.Cells.NumberFormat = "General"
    c = va
End Sub
 
Upvote 0
Solution
Akuini said:
@An Quala
Try this:
VBA Code: 
Sub toNumber()
Dim c As Range
    Set c = ActiveSheet.UsedRange
    va = c.Value
    ActiveSheet.Cells.NumberFormat = "General"
    c = va
End Sub
Click to expand...
It is working perfectly, I will be using this code multiple times in a VBA for different sheets, so should I copy paste this same code again with change in the sheet name for example sheets.sponsored products.UsedRange

Or I can do it maybe for all three sheets at once, sheet names are 'Sponsored Products', 'Sponsored Brands' and 'Sponsored Display'

Thank you.
 
Upvote 0
An Quala said:
Or I can do it maybe for all three sheets at once, sheet names are 'Sponsored Products', 'Sponsored Brands' and 'Sponsored Display'
Click to expand...
Try:
VBA Code: 
Sub toNumber1()
Dim c As Range, va, x
     For Each x In Split("Sponsored Products|Sponsored Brands|Sponsored Display", "|")
        Set c = Worksheets(x).UsedRange
        va = c.Value
        Worksheets(x).Cells.NumberFormat = "General"
        c = va
    Next
End Sub
 
Upvote 0
Hi, can you also add one more range to convert into "General" in the code, it is in 'Control Panel' sheet, Range D9 to the bottom until there is date and convert it into general as well, thank you.
 
Upvote 0
Hi @Akuini I want to add another range, sheet "Control Panel" Range("D9:D" & Cells(Rows.Count, 1).End(xlUp).Row), means as much as there is data below, so can you please add this range into your code as well,

Thank you.
 
Upvote 0
Sorry for the late reply.
Try this:
VBA Code: 
Sub toNumber2()
Dim c As Range, va, x
     For Each x In Split("Sponsored Products|Sponsored Brands|Sponsored Display", "|")
        Set c = Worksheets(x).UsedRange
        va = c.Value
        Worksheets(x).Cells.NumberFormat = "General"
        c = va
    Next
    
    With Sheets("Control Panel")
        Set c = .Range("D9", .Cells(.Rows.Count, "D").End(xlUp))
    End With

        va = c.Value
        c.NumberFormat = "General"
        c = va

End Sub
 
Upvote 0
Hello, facing a little error, "Runtime Error 9, Script out of range" at Set c = Worksheets(x).UsedRange
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
Posting Data with new form text boxes
Replies
0
Views
259
woodland81
W
H
  • Solved
Amend the VBA code to Filter Columns Instead of Rows
Replies
9
Views
164
Akuini
Akuini
M
  • Solved
Converting numbers stored as text to regular numbers query (VBA)
Replies
4
Views
439
Alex Blakenburg
A
P
  • Question
Applying conditional formatting to merged cells
Replies
1
Views
313
Joe4
Joe4
G
  • Solved
History of Dates to be traced in Excel (2010) in DD/MM/YYYY format
2
Replies
11
Views
394
MARK858
MARK858

Forum statistics

Threads
1,215,064
Messages
6,122,939
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top