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.
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.
Step 2
Insert a module in which we will write a VB function to format the number within a string in excel.
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 &.
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.
Step 5
Now drag the formula to get the formatted numeric values in your table.
You could edit/modify the code to suit your requirement.
Hope this helped.
