Split Text Into Columns Excel VBA Macro

[@ashokv2011]

worth of watching
superb bruhhhhhh.....!

[@singh_ma9676]

Really good video bro - I subscribed as you have some really nice macros and have explained them well - Keep up the good work. Questions:
1: What if two words had more than one space between them and we only want to count it as one space separator - so our split data is in columns next to each other. E.g. if Name and city had two spaces between them and the code will split and put name in Column A and then put City in Column C as it will put 2nd space in Column B - is there a way you can adjust the code so it caters for all spaces between two characters/words and they are right next to each other columns?
2: Do you have a video or macro for changing a text column into a number column - so that the value is read by Excel as a number? I searched in your video list but couldn't find one - maybe I missed it?
Thank you

[@ExcelMacroMania]

Thanks and welcome to the channel. To answer your questions:
1. Trim first the values to remove extra spaces with Trim worksheetfunction (not VBA Trim), before you split into columns:
For Each cell In Selection.SpecialCells(xlCellTypeConstants)
cell.Value = WorksheetFunction.Trim(cell.Value)
Next cell
2. You can use NumberFormat to change the format from text or whatever to number. That changes only the format - not sure if that's what you are looking for:
Columns("A").NumberFormat = "0" (or "0.00" to get a 2 decimals)
Columns("A").NumberFormat = "@" -> this would change back to text format

[@singh_ma9676]

@Excel Macro Mania Legend! Thank You!!

[@PiyushMedicalKnowledge]

Good video keep up bro

[@jrc430]

Hi, I am looking to see if it is possible to "reset" the textsplit function using VBA.
When I cut and paste from the page the textsplit is referencing I get the #REF error. Not cutting and pasting is not an option for me, so I was hoping to create a button that I can click and that would "reset" the textsplit criteria and replace the broken formula. This might require VBA.
example
=textsplit(Sheet1!A10, ", ") this continues down to (Sheet1!A1380)
For my worksheet purpose the button would reference column C10 through C1380 and reset the textsplit criteria referencing Sheet1!A10:A1380
Any insight or other work arounds would be much appreciated.

[@ExcelMacroMania]

I think you could use a loop, something like this below, but not sure is gonna work for so many rows.
For r = 10 To 1380
Set rng = Sheet1.Range("C" & r)
Sheet1.Range("B" & r).TextToColumns Destination:=rng, DataType:=xlDelimited, Comma:=True
Next r
Another possibility is using VBA Split function in the loop and then putting each part where you want.
Sub

[@rico401]

Hello Sir. How can I declare it to have the datatype be in text?

[@ExcelMacroMania]

DataType is a parameter of the TextToColumns function that can be either xlDelimited or xlFixedSize. There is no other option (not sure what you mean with text here). Note that we have text in the column anyway. Using xlDelimited splits the text for each cell into parts separated by that delimiter, which can be a comma, semicolon, space, tab, etc. You can also use a character as delimiter (not a string or text, maybe this is what you are referring to). A string (or text) consists of many characters, but here we can only use 1 character (for example, letter "a", or percent "/", etc). In that case you would use ... Other:=True, OtherChar:="/", as explained in the second example in this video.

[@AbhishekKumar-pg7cu]

Why it's not working in my excel

[@ExcelMacroMania]

That's probably because the text in your column is not separated/delimited by a space. Maybe is delimited by a tab, or a comma, semi-colon, etc, then you need to replace Space:=True, by Tab:=True, or Comma:=True, or Semicolon:=True, etc. If it is not delimited at all, and it has a fixed with, then you need to replace DataType:=xlFixedWidth. Hope that works!