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
Sort by date Sort by votes
No it's fine, sheet name was actually a little different, thank you!
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
You must log in or register to reply here.

Similar threads

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

Forum statistics

Threads
1,214,946
Messages
6,122,401
Members
449,081
Latest member
JAMES KECULAH

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