Trying to make a cell a certain amount of digits based on a number in another cell in Excel

0
votes

I am trying to make a formula that looks at one cell, lets say something like:

ABC-14

and from that use that number 14 to check if another cell has 14 digits as well. Is this even possible in excel, or is there some way I can do this?

An example would be looking at ABC-14, getting the number 14, checking if another cell is 14 digits, and writing true or false respectively in the cell.

Any help would be great.

3
excelexcel-formula
Is the number an integer? – Bathsheba
Yes, it's possible, but couple you please post what you've tried already, and what errors are you getting? – hnk
Well I'm pretty new to excel so I'm still researching what all the simple formulas do. The cell I'm trying to check is an integer like: 12345678, but I'm hoping I can check if that number has 14 digits in it or something like that. – Ganda

3 Answers

0
votes

If the number is an integer greater than zero then you can use

=ROUNDUP(LOG10(A1+1),0)

where A1 contains the number you wish to check.

LOG10 is the base 10 logarithm, the +1 offset is there for numbers like 100, 1000 etc and ROUNDUP does exactly what it says on the tin.

0
votes
Cell A1 =ABS-14
Cell B1 =12345678910123
cell A2 =ABC-13
Cell B2 =12345

place the below formula in C1

=IF(LEN(B1)=VALUE(MID(A1,SEARCH("-",A1)+1,LEN(A1))),"TRUE","FALSE")

and select B1 to B2 and press CTRL + D

Hope you expecting this one?

0
votes

The way to check how long a cell is is =LEN().

The way to separate the numerical values in your mixed string is to take your cell length, minus the find of your dash (assuming all your values have dashes), to leave what's left on the right (in this case, 14).

The other issue, is that it will return the string value 14, not the number 14. If you multiply it by 1, it'll then know it's a number (without changing the value).

So, putting all that together, let's assume your ABC-14 is in cell A1. Let's assume your numerical string is in B1.

In C1, we'd write =IF(LEN(B1)=(RIGHT(A1,LEN(A1)-FIND("-",A1)))*1,"true","false")