0
  • I have 2 columns A and B.
  • Column A is the list of numbers of 8 numbers, column B is the list of numbers that just have 1 number

A1= 12345678 B1= 9

A2= 123456789 B2= (EXTRACT(A2;9;1))

But there are some cells from column A that have 9 numbers, so the last number has to go in column B.

I know how to use extract but if I run the function I have cells that are already fixed so it doesn't work. So I just need a function that doesn't erase the number of certain cells in column B.

2
  • 1
    I haven't encountered the EXTRACT function; is that an add-on or a feature in a very recent version? B2 cannot contain both a constant and a formula. You could use a helper column to look at what's in B, and if it's blank and A has 9 characters, use the 9th character. Those values could be copied back to B, or you could just use the helper column rather than B. An alternative would be to use VBA to perform the same determination and replace appropriate values directly in B.
    – fixer1234
    Jun 23, 2019 at 1:53
  • At the end I used a helper column to fix it Jun 23, 2019 at 14:55

1 Answer 1

1

I cannot find EXTRACT on the Excel documentation, so you must be using either a custom function or a plugin.

If I understand correctly, you have

  • col A: some 8 or 9 digit integers
  • col B: the 9th digit from the values in col A

Like this;

| Source   | 9th Char |
|---------------------|
|70975090  |          |
|104616264 | 4        |
|77365002  |          | 
|74857908  |          | 
|80749739  |          |
|25274759  |          |
|28389812  |          |
|104577673 | 3        |

If so, the values in col B would then be generated by this formula.

=IF(LEN(A2)>8,RIGHT(A2,1),"")
4
  • This doesn't quite reflect the question. Column B contains either a single digit or a blank. It isn't stated whether the single digit is necessarily related to what is in column A. But the last sentence of the question states that the action should not happen if column B already contains a number. Column B can't both contain a number and a formula to test column A or itself. So what is in this answer could be part of a formula in another column that picks from B, or if B is blank, picks the 9th character in A if there is one.
    – fixer1234
    Jun 24, 2019 at 22:44
  • Agreed. Perhaps Javiera Nunez could clarify? Jun 25, 2019 at 8:22
  • The example that you made is correct, but I need 8 numbers in column A and the last number (9) has to be in column B. I was trying do it without a helper column because I need numbers. I need numbers because I have to use =Vlookup and my reference has to be column A, and right now I have a function in there so, it doesn't work. Jun 25, 2019 at 18:19
  • I think you will need a Source column, then an "8 digit" and a "9th digit" column. In the "8 digit" column, you can use =left(A2,8) to return the left 8 digits. Jun 26, 2019 at 8:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .