Forum Discussion
Automatic Text To Column for Multiple Column
Hello Everyone,
I need to find a way to use text to column feature for multiple column for a table before I use that excel book for another macro enabled excel book.
I am adding the table as an example. Problem is that I get this excel from SAP and I need to use this excel for another excel book which has macro. But of course macro gives an error if I first did not use text to column button for 4 or 5 column and save it. I am looking for a solution to make this step completely go away or a bit easier.
In attached file, I always need to click text to column button for column A, B, G, V, W and AB.
https://1drv.ms/x/s!Ar6QmGgwrwGjlzBndhkMlYtXN2b-?e=hCaHox
Thank you for all your help.
5 Replies
Hi ArdaOzguler
Alternatively with Power Query:
- Select all data
- From Home tab > Format as Table
- From Data tab > icon From Table/Range (Power Query Editor opens)
- Icon Close & Load (top left in the toolbar)Sample attached
- vba?
rng=application.union(range("a2:a700"),range("b2:b700"))
for each r in rng
'convert text to number
r.value=Val(r.value)
next- Hello peiyezhu,
Thank you for your response. Could you please explain it to me to apply in my situation? I am not sure that I can do it in VBA without any guidance 🙂
Thank you.- To apply the VBA code in Excel, you need to follow these steps:
1. Open the Excel workbook where you want to apply the code.
2. Press `Alt + F11` to open the Visual Basic for Applications (VBA) editor.
3. In the VBA editor, go to `Insert` in the menu and click on `Module`. This will insert a new module in the project.
4. Copy and paste the provided VBA code into the module.
5. Modify the code if necessary, such as changing the sheet name or range.
6. Close the VBA editor by clicking the close button (X) or pressing `Alt + Q`.
7. Press `Alt + F8` to open the "Macro" dialog box.
8. Select the macro name "ConvertStringToNumber" and click on the "Run" button.
The VBA code will then execute, converting the string values to number values in the specified ranges of columns A and B. Make sure to save your Excel workbook after applying the code.
