Separate Text and Numbers in Excel (4 Easy Ways)

In this video, I will show you four easy ways to separate text and numbers in Excel.

The following four methods are covered in the video:
1. Using Flash Fill
2. Using a Formula
3. Using VBA
4, Using Power Query

-----------------------------------------------------------------------------------

Below are the two VBA codes I have shown in this video:

Function GetNumber(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetNumber = Result
End Function

Function GetText(CellRef As String)
Dim StringLength As Integer
StringLength = Len(CellRef)
For i = 1 To StringLength
If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)
Next i
GetText = Result
End Function

-----------------------------------------------------------------------------------

00:00 Intro
00:26 Separate Text and Numbers using Flash Fill
03:36 Separate Text and Numbers using Formula
08:03 Separate Text and Numbers using VBA
11:37 Separate Text and Numbers using Power Query

#Excel #ExcelTips #ExcelTutorial

[trumpexcel]

If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos.

Also, I have made all of my Excel courses available for free. You can check these out using the below links:

[blackout]

No BS. Explanations are crystal clear. Multiple solutions proposed. 10/10 video, you get a like and a sub.

[TalmadgesArcade]

My hero. Hit like 5 other sites trying to do a special split. Lots of crazy over complicated foruimlas that wouldn't work for me. This guy, this guy gets it! THANK YOU! it took me 20 seconds to teach flash fill and bam, my list is spit.

[frankabacus]

One of my favourite Excel master - explanations are clear and practice file attached. Thank you so much

[athena]

THANK YOU SO MUCH FOR THE FLASH FILL EXPLANATION! You have no idea how happy I am right now!

[anthonylandrum]

Flash Fill, OMG, that was cool. I have 69, 682 rows, you saved me a lot of work!!!! Thank you. Happy New Years!

[IvanCortinas_ES]

Excellent treatment of these cases Sumit. They are highly requested examples by companies. Thank you very much for this great video.

[keithbray]

Amazing!! This saved me so much time in my new job. Thank you 🙏

[mili]

the first technique was exactly what i needed, it did 95% of the work just gotta check a few

[usmanalimuddin]

MAGIC !!!
I have been using left right formula for the years- this is something I can use it if the pattern in cells are different -Bravo

[NEGPOS]

Fantastick! Love your explanation ... Thank You ...

[firasath]

You are genius brother, I love your videos. Very informative, simple and to the point.👍

[thulisilegumede]

Thank you very much saved me from spending hours to minutes 🤛

[shiffamohammed]

Thanks a lot Sumit, for the awesome excel tricks you shared.

[dgitson]

This is a a great demonstration of alternative techniques, thanks.

[frachiar]

11 / 5.000
I point out that in my excel 2016 I must press Ctrl+Shift+Enter in order to have the result, but it works. Great!! 🙂 Thank you!!

[tunaikinyanguk]

great as always.

formula FIND({0, 1, 2, 3, 4, 5, 6, 7, 8, 9} may alternatively be expressed as FIND(SEQUENCE(, 10, 0) in excel 365

[SirShehu]

Thanks man, you saved me lots of work today

[mohammadalbizri]

Thankful, hope giving more lessons about power query

[HowToAnalyst]

Thank you Sumit! I'm using method #2 with a formula.