Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote -1 down vote favorite

I want to determine if a cell has one of these number formats within the cell:

###.##
###
###.#

Example:

048.4

The data is most likely a string, as it came from a PDF.

I'm trying to use Find() but it doesn't appear to accept regex.

What's the best solution for this, while trying to avoid 3rd party plugins?

share|improve this question

2 Answers

up vote 0 down vote

One way would be to use the formula =CELL("format",A1) for each cell of interest (changing A1 accordingly) which after the F shows the number of decimal places. (But I'm not sure how you got your example to start and display 0!)

share|improve this answer
The data came from Adobe's "Save As Excel" feature. – JBurace Oct 15 '12 at 20:55
So presumably is not formatted as Excel's idea of ###.## etc but as text? In which case try =LEN(A1)-FIND(".",A1) instead (returns an error when there is no 'decimal point'). – pnuts Oct 15 '12 at 21:01
Yes but that'll still match on sentences with periods in them, which this file contains. – JBurace Oct 15 '12 at 21:05
In that case convert from text to number format first? – pnuts Oct 15 '12 at 21:07
The cell contains more than just those numbers though, it also can contain text (which is why I said the cell "contains" and not "equals") – JBurace Oct 15 '12 at 21:08
show 1 more comment
up vote 0 down vote

  1. To identify your format it is sufficient to give the number of digits after decimal separator. To do this you can use formulas pasted in colums B and C enter image description here

  2. To force Excel to treat data in a cell as number you can perform any numerical operation on the cell. Try e.g. adding zero, or multiplying by 1. It will only work if in your locale decimal separator is the same as in text, . in your case. If it isn't you need to replace dots with commas. In the above example, I replace the dot with comma. If you don't need to, just add zero to input value in column A.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.