how do I add decimals to a string of numbers
for example i have a number in this format 01010101010101 and I need it to be like this 01.01.01.01.01.0101
how do I add decimals to a string of numbers
for example i have a number in this format 01010101010101 and I need it to be like this 01.01.01.01.01.0101
CTRL + H
find .
replace ,
replace all
Do you have questions, on this item, just ask.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
the problem is that there are about 100 different numbers that I need to reformat with decimals so find and replace won't work. Any other thoughts?
jham
Select the whole area you want to find and replace and then do the steps as told in #2.
All cells will be changed.
Please reply if this solved your problem.
Are you always using the same pattern? Using always 14 numbers and want always them to be separated as you showed?
Yes I want the same pattern for all of the numbers.
The look and replace won't work because there is nothing to "look for" in order to replace it with a "."
thoughts?
Column A is made on the way i descibed it.
Is this the wanted result?
thank you oeldere.
However your column on the left has comma's but their are no comma's in my numbers.
I have about 100 rows with numbers in this format 01010101010101 and I need it to be like this 01.01.01.01.01.0101
Can you upload a sample. I tihnk I might have a way to do it. If its always 14 numbers and you always want xx.xx.xx.xx.xxxx you might be able to use this formula, a little long but i tihnk it works
I did this with A40 as my target, just put this formula across from your target in a different column, replace A40 with your first cell and drag downPlease Login or Register to view this content.
YES! that did the trick. Thank you!
If the question is solved, will you mark your question as solved?
If you have (other) questions, just ask.
@ jimmyhamic
Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.
Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
If the numbers are 14 digits, try also,
=TEXT(A1,"00\.00\.00\.00\.00\.0000")
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
if your "numbers" are in the exact format that you provided (starting with a zero), then they are really strings, in which case you probably are better off using @haseeb_a's solution.
if your numbers are truly numbers - starting with digits 1 through 9 - then you can use a Custom format to achieve your desired results, without the need for any formula.
the Custom format that would display the number 10101010101010 as 10.10.10.10.10.1010 would be:
mind you, this will only work if your entry is a number.Please Login or Register to view this content.
Custom format can be set in the screen depicted below:
jimmyhamic_848121_27jul12_01.PNG
and this window can be launched by right-clicking on a cell and selecting Format Cells.
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
I see that this question was marked "solved" can you tell me where it is that I find the tool to mark it solved (for my future reference). For some reason I don't see it.
Here you go.
New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks