Skip to main content

How to add period after numbers list in Excel?

Author: Sun Last Modified: 2017-04-25
doc add period after number 1
If you have a number list in a column, and now you want to add period sign or right parenthesis in the end of every number, as below screenshot shown, how can you quickly solve it in Excel?
Add period after number with formula
Add period after number with Format Cells
Add period after number with Kutools for Excel good idea3

Add Text (add character/word to a specific location of each cell in a range.)

doc add text 6

Add period after number with formula

To add period after number, you can use a simple formula.

Select a cell next to the number, and type =A1&"." Into it, and then press Enter key, then drag the autofill handle down to the cells needed this formula.

doc add period after number 2

If you want to add right parentheses to the end of the number cell, use this formula =A1&")".


Add period after number with Format Cells

You also can use Format Cells to format the cell as a custom format.

1. Select the number list, and right click to select Format Cells from context menu. See screenshot:

doc add period after number 3

2. In the Format Cells dialog, under Number tab, click Custom, then type #. Into the Type textbox. See screenshot:

doc add period after number 4

3. Click OK. Now the periods are added after numbers.

Tips:

(1) If you want to add right parentheses to end of number, type this #) into the Type textbox.

(2) If there is only 0 in a cell, this method will show 0 as . or ).

(3) If there are not only numbers in the cell as below screenshot shown, the above method cannot work correctly.

doc add period after number 5


Add period after number with Kutools for Excel

If you have Kutools for Excel, you can use the Add Text utility to easily add any text in any location of cell.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

After free installing Kutools for Excel, please do as below:

1. Select the numbers, and click Kutools > Text > Add Text. See screenshot:

doc add period after number 10

2. In the Add Text dialog, type period sign into Text box, and check Specify option, then type 1 into the textbox under Specify. See screenshot:

doc add period after number 7

3. Click Ok or Apply. Now the periods add added after number

doc add period after number 8

Tips:

(1) This method can’t add period sign to cells which only contain one character of number, such as 1, 2, etc.

(2) To add right parenthesis after numbers, please type ) into Text box of above dialog.

(3) With Add Text utility, you also can add period before every number of a string. See screenshot:

doc add period after number 9

Click here to know more details about Kutools for Excel’s Add Text.

pay attention1If you are interested in this addi-in, download the 60-days free trial.

good idea4Remove Characters

Kutools for Excel's Remove Characters utility can help you remove the characters you don't need, such as remove numeric string, alpha string, non-numeric string and so on.
doc remove characters

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (19)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How do I tell excel to put a period in a blank cell and ignore cells that have numbers?
This comment was minimized by the moderator on the site
Hi, Laura Z, if you want to add a period to all blank cells in a range, just select the range, then press Ctrl + G to enable the Go To dialog, and click Special to the Go To Special dialog, check Blanks option and click OK. Now all blank cells in the range are selected. click a period in the formula bar, press Ctrl + Enter key, all blank cells are filled with a period.
This comment was minimized by the moderator on the site
tenho na barra de formulas o seguinte valor PMA R$ 1354,87
preciso converter para que o numero fique assim PMA R$ 1.354,87
COMO DEVO FAZER?
This comment was minimized by the moderator on the site
Hi, PAULO SERGIO MACHADO, sorry that I only can reply with English. I have not found a formula can solve your problem. But there is a roundabout way can help you. Using Text to Columns to split your string into three columns based on blank, one is PMA, one is R$, the other one is number, then format the number column as Comma Style (Home tab, Number group), then combine them together with formula =A1 &A2 &A3, A1, A2 and A3 are the three split cells.
This comment was minimized by the moderator on the site
Bom Dia! Eu preciso formatar os numeros que está como 2.013.333.444.555 e arrumar para que mostre 2013.333.444555, como posso fazer?
This comment was minimized by the moderator on the site
Hi, Giagia, try this formula=SUBSTITUTE(SUBSTITUTE(N16, ",","",1), ",", "",SUM((LEN(N16)-LEN(SUBSTITUTE(N16,",",""))))-1), N16 is the cell you use. Hope it can help you.
This comment was minimized by the moderator on the site
hi . i want to convert number as example 1420 to 14200 how do this please help me
This comment was minimized by the moderator on the site
HI I WANT CONVERT NUMBER AS EXAMPLE 3051 to 305.1
HOW TO DO THIS
This comment was minimized by the moderator on the site
=LEFT(A1,3) & "." & RIGHT(A1,1)
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations