Separate Text (Characters) & Numbers from Alpha Numeric String Using Formula and Macro (VBA)

Have you ever come across the data where the text (characters) and numbers are put together in a single cell. Like Employee Name and Employee Code, Customer Name and Customer Number, etc.

If the answer is Yes and you want to learn how to separate them using formula then this post is definitely for you.

This type of data comes into three flavours:

1) Text and Number (Sujeet123)

2) Number and Text (123Sujeet)

3) Text and Numbers tangled (S1u2j3e4e5t6, 1s2u3j4e5e6t)

We can separate the first 2 types using the formula and for 3rd type, we need to write a few lines of code (Macro).

Let's start with the First one:

1) Text and Number (Sujeet123)

Ex-1

Let's assume that Cell A2 holds Sujeet123 in Sheet1.

we will use the below formula with the Left and Right function to separate the Text and Number.

=Min(find({01,2,3,4,5,6,7,8,9},A2&"0123456789"))

The above formula will give us the starting position of the first Number.

To find the Text Part put the below formula in Cell B2:

=Left(A2, Min(find({01,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

Logic

Min(find({01,2,3,4,5,6,7,8,9},A2&"0123456789"))-1 = 7-1 = 6

=Left(A2,6) = "Sujeet"

To find the Number Part put the below formula in Cell C2:

=Right(A2, len(A2)-Min(find({01,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)

Logic

len(A2)-Min(find({01,2,3,4,5,6,7,8,9},A2&"0123456789"))+1 = 9-7+1 = 2+1=3

=Right(A2,3) = 123

2) Number and Text (123Sujeet)

Ex-1

Let's assume that Cell A3 holds 123Sujeet in Sheet1.

we will use the below formula with the Left and Right function to separate the Number and Text.

=MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},Upper(A3)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))

The above formula will give us the starting position of the first Character.

To find the Number Part put the below formula in Cell B3:

=Left(A3,Min(Find({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},Upper(A3)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))-1)

Logic

Min(Find({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},Upper(A3)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))-1

=4-1 = 3

=Left(A3,3) = 123

To find the Text Part put the below formula in Cell C3:

=Right(A3,len(A3)-Min(Find({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},Upper(A3)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))+1)

Logic

len(A3)-Min(Find({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},Upper(A3)&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))+1

=9-4+1 = 5+1 = 6

=Right(A3,6) = "Sujeet"

3) Text and Numbers tangled (S1u2j3e4e5t6, 1s2u3j4e5e6t)

Let's assume that Cell A4 holds S1u2j3e4e5t6 in Sheet1.

To separate Text and Numbers for this kind of data we will write a user-defined function. User-defined functions work just like excel built-in functions Like: Left(), Right(), Min(), Vlookup(), etc.

We need to put the below code in a module in VBE (Visual Basic Editor)

Use Keyboard Shortcut "ALT + F11" to open the VBE. Right-click on Sheet1 from Insert select Module. It will create a new module with the name Module1. Paste the below code in Module1 and save the workbook in Excel Macro-Enabled Workbook ".xlsm" format.

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

Function Separator(rng As Range, typ As Integer)

Dim str As String

Dim i As Integer

'If typ = 1 then separate Characters only

If typ = 1 Then

For i = 1 To Len(rng)

If Not IsNumeric(Mid(rng, i, 1)) Then

str = str & Mid(rng, i, 1)

End If

Next

'If typ = 2 then separate Number only

ElseIf typ = 2 Then

For i = 1 To Len(rng)

If IsNumeric(Mid(rng, i, 1)) Then

str = str & Mid(rng, i, 1)

End If

Next

End If

Separator = str

End Function

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

Here we have created a function that has 2 arguments, 1st one is rng which takes cell reference of the cell which holds the string and 2nd one is typ which take number 1 or 2. 1 is for separating Character only and 2 is for separating Number only.

Pros: User-defined function work just like Excel built-in functions.

Cons: UDF does not show us the arguments whereas Excel built-in functions show us the arguments. Here you need to remember the arguments and the kind of data they accept.

Put below formula in Cell B4 for separating Text (Characters) only.

=Separator(A4,1)

="Sujeet"

Put below formula in Cell C4 for separating Numbers only.

=Separator(A4,2)

=123456

To understand this topic in more detail, watch the video using the link given below:

Video Link- https://youtu.be/6vlv-FFMZhM



Comments

Post a Comment

Popular posts from this blog

Power Automate - Automatically fetch data from Power BI in to Excel and Send the copy of the Excel file via Email

Image
Power Automate - Automatically fetch data from Power BI in to Excel and Send the copy of the Excel file via Email Step-1 Step-2 Step-3 Using Command text we can make changes in the data we fetch from the Power BI. Like add fields/columns, filter data, rename columns. It is a DAX formula. Step-4 Step-5      Step-6 Step-7 Posting message on Teams. We can post message to a Teams Group  also by selecting Group chat from Post in and select the Teams Group from the Group chat  dropdown that will appear after making selection. Step-8 Location:  We need to select the Group  on SharePoint in which our file is located. Document Library: We need to select the Documents option from the dropdown. File: Select the file on which you want to run the script. Script: Select the script to perform the desired action. Step-9 Step-10 Step-11 Using Filter Query  we can decide which file properties we want to get. We are performing this step to attach excel as an attach...
Read more

Function to Paste Excel Range on Outlook Mail Body (In text not image) Function Name - rngHTML()

Use the below function in your subroutine in the below mentioned way: .HTMLBody = str & rngHTML(Sheet1.Range("a13:g17"))  & .HTMLBody ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Function rngHTML(rng As Range)     Dim fso As Object, ts As Object, TempWB As Workbook     Dim TempFile As String     TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"     '' copy the range and create a new workbook to paste the data into     rng.Copy     Set TempWB = Workbooks.Add(1)     With TempWB.Sheets(1)         .Cells(1).PasteSpecial Paste:=8         .Cells(1).PasteSpecial xlPasteValues, , False, False         .Cells(1).PasteSpecial xlPasteFormats, , False, False         .Cells(1).Select         Application.CutCopyMode = False         O...
Read more