CODEWITHSUNDEEP
excelexcel-formula
Jean Rauber
Jean Rauber

Reputation: 1

Using a function in a full cell

The problem:

I have a (big) column of numbers, most of which are 3-digit, some of them are 2-digit. I'd like to replace every two-digit number with first 3-digit number above.

My question:

Is there a way to do that? I was thinking of assigning a function to each cell in a column. This function would replace the value with a previous 3-digit if the value in cell was 2-digit, BUT do not change the value in the cell if it was 3-digit. But how do I insert a function into a full cell?

Upvotes: 0

Views: 49

Answers (2)

OverflowStacker
OverflowStacker

Reputation: 1338

A possible solution would be: ARRAY-FORMULA: CTRL + SHIFT + ENTER

=IF(LEN(A1)=3,A1,INDEX($A$1:A1,MAX(IF(LEN($A$1:A1)=3,ROW($A$1:A1)))))

enter image description here

Upvotes: 0

bosco_yip
bosco_yip

Reputation: 3802

Assume data housed in column A with header in A1

and, A2 must started with 3-digit number

Then,

Formula in B2, copied down :

=IF(LEN(A2)=3,A2,B1)

Upvotes: 1

Related Questions