Formula to place a decimal

[mhamilton703]

I'm using Office 2010.
I have numerous columns of data over 5000 rows that I need to place a decimal 2 places in from the left in (I.E. 41555555 => 41.555555). What formula would I use to do this?
Another example:
Current- What I need it to look like-
41555555 41.555555
42111111 42.111111
42999999 42.999999

Any help would be greatly appreciated!!
Thanks!

[Jonmo1]

Are ALL the numbers the same length (8 digits) ?

If so, try

=A1/1000000

[hoyasaxa215]

=text(a1,"##.#####,,")

[mhamilton703]

Yes, in each of the columns the legth of numbers is the same, but different through the other columns (Column A is 8 digits where Column B is 9 digits).
I'm feeling kind of stupid here, hahaha.....but
I've tried both of these and am getting a "circular reference" error. I've tried setting formula in a different column, a different cell, clicking the entire column with the same error. I've also gotten the "#VALUE!" error. Not sure why this is happening

[Jonmo1]

Post a sample book.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[joeu2004]

I'm using Office 2010.
I have numerous columns of data over 5000 rows that I need to place a decimal 2 places in from the left in (I.E. 41555555 => 41.555555). What formula would I use to do this?

Yes, in each of the columns the legth of numbers is the same, but different through the other columns (Column A is 8 digits where Column B is 9 digits).

If your data is in A1:A5000, then in an unused column starting in the same row (e.g. X1), enter the following formula and copy down the column:

=IF(A1="","",SIGN(A1)*TEXT(A1,"00\." & REPT("0",LEN(A1)-2)))

If you want to replace A1:A5000 with those values, copy X1:X5000 and paste-special-value into A1:A5000. Ignore what you see in X1:X5000 subsequently. Simply delete the formulas in X1:X5000.

That assumes that the numbers in A1:A5000 never have more than 15 digits.

Repeat the procedure for each column, replace A1:A5000 with the next range.

If this is too tedious to do manually for "numerous" columns, would want to use a VBA macro?

PS: The IF(A1="","",...) part of the formula avoids mistakes where you apply the "conversion" to blank cells in column A. But it would be better to ensure that you only have as many formulas in column X as you have numbers in column A. The IF(A1="","",...) part can be left as-is; it would become ineffective. The problem I am trying to avoid is: if you have more conversion formulas than you have numbers, some of the results in column X would be the null string (""), as intended. But when you copy-and-paste-special to overwrite column A, the cells in column A that were empty initially and still appear to be empty would now actually contain the invisible null string. That can affect other formulas. For example, COUNTA(A1:A5000) might return an expected result.

[mhamilton703]

So here is a screenshot of the before in Columns H and I:
ExcelSSBefore.jpg

And this is what I want it to look like after:
ExcelSSAfter.jpg

[Jonmo1]

Pictures aren't really helpfull.
We can't copy/paste the values into our own sheets to work with them.
I'm certainly not going to type them in by hand..

Can you post an actual excel workbook?

[hoyasaxa215]

Insert a column inbetween Column H and Column I, so that your new Column I is now blank. In cell I3 (which should be the blank cell next to 41853889) enter this equation: =TEXT(H3,"###.#########,,"). You can then copy the formula all the way down. Repeat for the second column of data, just change the cell reference (H3) to the cell you want to convert.

[joeu2004]

So here is a screenshot of the before in Columns H and I:
Attachment 331325

And this is what I want it to look like after:
Attachment 331328

Not all the numbers in the latter ("what I want") have the "decimal 2 places in from the left", as you required initially. Is it really the case that all the numbers should have 6 decimal places?

If that is the case, enter 1000000 into an unused cell (e.g. X1), copy it, select the range of numbers to convert (e.g. A1:A5000), right-click and click on Paste Special, select Divide, then click on OK. Then format the range as Number with 6 decimal places.

[mhamilton703]

Sorry about that...got several things going on and didn't think about that....
Here is an edited version of what I'm working on!

[Jonmo1]

OK, it 'looks like' you don't actually want the . after the 2nd number (you have it after the 3rd in some

Would it be more accurate to say you want the . BEFORE the last 6 numbers?
At least according to the pictures and xl file it appears that way

[FDibbins]

It looks like you already have what you want in column H?

Which other columns do you nered to "fix"? Also, can I ask why you need to do this in the 1st place?

edit: OK I see that only a few are done

Try this...
put 1000000 in a cell on its own
with the cursor still on that cell, press CRTL C (copy)
Now, highlight the range you need to apply this to
Click the Paste dropdown arrow and select paste Special
check Divide
click OK

[mhamilton703]

Yes it would be safe to say that I need the "." before the last 6 numbers. I did the first 18 lines manually of both H and I manually, still have over 3900 lines to do.
I work for a Cell Phone Company and I need this to set some parameters within software that we use to test and report our sites.

[Jonmo1]

See the 'adjusted' values in columns J and K

I used =REPLACE(H3,LEN(H3)-5,0,".")

EFmhamilton.xlsx

[mhamilton703]

That's awesome! Will definitely file that formula away for future reference!!
Thank you everyone for all of your help and patience! It is very much appreciated! Hope everyone has a great day!

[Jonmo1]

You're welcome.