Technical Solution

[OpenOffice Calc] How to replace cell value automatically if the cell value is a number

Anju.r | | 2 min read

Suppose you are working with OpenOffice Calc which requires lot of filters. And you want to replace a cell value with a digit to some text or value in another column. If this is the situation, then you can use a combination of IF() and ISNUMBER() to accomplish this task.

The syntax of if is
IF(logical_test, [value_if_true], [value_if_false])
ISNUMBER(value)

So let us check how this can be applied in our sheet.
Suppose you have email field in column A which consists of both email values and digits. And you have phone number in second column i.e. Column B. You are appending values in column C.

  1. Case 1: Insert value 'Digit' in Column C if the cell value in A1 is a digit, Otherwise insert the text 'Not Digit' in Column C.
    So insert the function in C1 as =IF(ISNUMBER(A1),"Digit","Not Digit"). This will check whether A1 is digit or not. If it satisfies it will insert 'Digit' in C1 , else will insert 'Not Digit'. Right click on the c1 will append the remaining values in all the cells.
  2. Case 1: Insert value in A1 in Column C if the cell value in A1 is a digit, Otherwise insert the value in B2 in Column C.
    So the function in C1 becomes =IF(ISNUMBER(A1),A1,B1).

Please note that double click on the right end of c1 will not work properly if there is empty cell in column A. In that case you have to drag the cell to obtain the value.

 

 

Related Topics