How do I add a prefix to a column of numbers?

ChessGM wrote:

AIs there a way to apply this formula just for the filled rows/cells and leave the empty ones?

One way is to do something like this:

=IF(ISBLANK(A2),"",A2+10^(INT(LOG10(A2))+1))

It's the original formula, but adding an IF statement to test if the cell is blank.

=IF(<condition>,<what to insert if condition is true>,<what to insert if condition is false>)

If the value in column A is blank then the formula inserts a "blank" (the ''"). If it is not blank then the original formula applies. If you're using Barry's formula you would put that in the last part of the IF statement instead.

SG

There is. Actually, there a couple o methods, depending on the result you want.

Here is an example showing both results:

Column A shows a series of numbers.

Column B shows the same series of 'numbers with a 1 appended to the front of each number. The formula used, entered in B2, then filled down, is: =1&A2

Notice that the 'numbers' are aligned to the left side of the cell, the Automatic alignment for text values. That's because the & is the concatenation operator, which joins the text string on its left to the one on its right. The result is a text string.

This is fine if you are appending '1' to the beginning of 'numbers' such as stock number, model numbers, or serial numbers which are already a mix of numerals and letters (and sometimes symbols), making them already a text value rather than a numerical one.

But if the result is to be an actual number, not just a string of text characters, You'll need to wrap the short formula above in the VALUE function, which reads that text string, and returns the number it represents. The formula in column C, entered in C2 and filled down, is: =VALUE(1&A2).

Formatting Column A to include the thousands separators causes these changes:

The formatting puts commas into he correct places in the original value, but the concatenation that appends the 1 to the beginning of the 'number' reads this as text, and includes only the commas that are displayed in the numeric value in column A ; fine in most instances, but fatal in examples such as rows 6 and 9, where the result requires one more comma—one that is NOT included column A, as it is not necessary there, but that is necessary for VALUE to recognize the string as a valid number IF there is already at least one such separator in the string.

That difficulty can be bypassed by NOT including 'show thousands separator' in column A (or in the text results column( B)) but including it this in the numerical results column (C)

Note that the two results columns are independent of each other. You will need only two columns—the column containing the original values and the one showing the results as you want them.

Regards,

Barry

Another formula, that works with "true" numbers (right-aligned by default) or numbers "as text" (left-aligned by default) and doesn't care whether the "numbers" are formatted with or without , separators.

In B2, filled down:

=A2+10^(INT(LOG10(A2))+1)

SG

You enter a formula (Barry's or mine will work fine) in one cell. Then place your cursor over the cell and you should see a yellow dot in the bottom edge. Drag that dot down to "fill" the formula into the cells below.

If you're just starting out with Numbers be sure to have a look and Numbers Help in your menu. Here's the explanation there on "filling" or "autofilling."

SG

Once you have the second column with the "corrected" numeric values,

select that column

copy

select the original column

select the menu item "Edit > Paste Fomula Result"

then delete the second column