Format Number In A String Without Extracting In Excel

In this post we will see how to format a number in a string.

Let’s see an example data where we have numeric part with string and we want to format the number to round number and present the result with the string concatenated in it.

As you can see in the data below, we have the data to be formatted in column A, and we want to do round number formatting to the string value.

table showing data embedded in a string which we want to format

As you could see in the data we have a numeric part and a string separated by space.

In your data, you might be having some different scenario, but here I am just presenting a basic example so that you can get a idea of how to achieve the result of formatting a number within a string.

Step 1

We will create a user defined function in excel to format the number within a string.

Open VB editor from Developer tab in excel as shown in the figure.

open vb editor to write function to format the number in a string

Step 2

Insert a module in which we will write a VB function to format the number within a string in excel.

insert module to create function to format number in a string

Step 3

Now double click the newly created module to open that and paste the below code in the module.

Function teste(txt As String) As String

 pos = InStr(1, txt, ” “, vbTextCompare)

 num = Left(txt, pos – 1)

 format_number = Round(num)

 textpart = Right(txt, Len(txt) – pos)

 teste = format_number & ” ” & textpart

End Function

I will explain what each line in the code is doing.

  • txt As String-takes the argument which is your value.


  • pos = InStr(1, txt, ” “, vbTextCompare)-finds the position of space “ ” in the value.


  • format_number = Round(num)-Rounds the number/Formats the number in the value.


  • textpart = Right(txt, Len(txt) – pos)-Extracts the text part after the space.


  • teste = format_number & ” ” & textpart-Returns the formatted number with the text concatenated in it with the use of concatenation operator &.

function to format number in a string

Step 4

Now enter the formula as shown in the figure and press enter, A2 is the value which will be passed to the function that we have just created.

table showing function to format number in a string

Step 5

Now drag the formula to get the formatted numeric values in your table.

table showing formula has formatted the number in a string

You could edit/modify the code to suit your requirement.

Hope this helped.

Share The Knowledge

Random Posts

  • Color Alternate Rows In Excel

    Let’s see how we can color each alternate rows in excel. We could use the MOD and ROW function in […]

    Share The Knowledge
  • Hyperlink In Excel Based On Cell Value

    In this post, we will see how we could conditionally show hyperlink in a cell based on value of a […]

    Share The Knowledge
  • Create Checklist In Excel

    Let’s see how to create a checklist in excel to know the current status of the action items. Below checklist […]

    Share The Knowledge
  • Remove Duplicate In Excel

    We have Product data in column A and you can see there are a lot of duplicates values, we want […]

    Share The Knowledge

Leave a Reply