How to extract a number from a text in a string?

Hi Pam,

My assumptions on this are that:

1. Your computer is set for a region in which the decimal separator is a comma ( , )
2. Each of the numbers in column A of the example is a number greater than 1 and less than 100
3. The total length of the string (digits, comma and letters) is 16 characters. (The string ending EUR has an 'extra' zero to keep its total length the same as the others)
4. You want to extract the first eight digits, plus the comma from each string.
5. You want to express the extracted string as a number.

In the table below, the text strings from your example have been pasted into columns A and B.

Columns C and D contain a formula that shows the position of the first letter in the strings in columns A and B of each row. Note that the EUR string starts one character later than the four letter strings in the other rows.

Columns E and F contain formulas that extract the first nine characters (8 digits plus the decimal separator) from the strings in columns A and B respectively. The formulas in row 2 of these columns also converts the resulting string to the number that string represents, making that result useable in further calculations. Copies of the formulas are shown and discussed below the table.

Formulas:

These are presented as entered in my example, in a region where the decimal separator is a period, or 'full stop' ( . ).

For regions where the decimal separator is a comma ( , ), change the commas in the formulas to semi colons ( ; )

Columns C and D: These are optional, and are not needed to return the results you are looking for.

C2: FIND("L",A2)

Fill right into D2, then change the letter L to U

Select both cells and Fill down to row 6. Replace the L in the column C formulas with the first letter in the corresponding row of column A.

Columns E and F: Two versions. The first returns text strings representing the numbers formed by the first 8 digits plus the decimal separator in the same row of columns A and B.

E2: LEFT(A2,9)

Fill the formula right to F2.

Select both cells and fill down to row 6.

Results will be as shown in cells E3 and F3 to E6 and F6.

These are fine if the results are to be viewed and interpreted by humans, but if they are to be recognized by formulas and used in calculations further down stream, you will want to convert these strings to numbers, using this revised version.

E@: VALUE(LEFT(A2,9))

Follow the same Fill and edit instructions as listed above. instructions

Note that in the example table, the only cells in columns E and F containing "numbers" are those in row 2. Close examination o the original text in this row of columns A and B will show that I have edited them to replace the comma with a period to make the VALUE function recognize the nine characters as a 'number'. In your region, changing the comma in the formula to a semi colon is necessary to make the formula work with numbers where the decimal separator is a comma.

Setting the length of a number may be possible using a custom formula expressing the number in base 10 with a specific number of digits, but I've not tried it. You can achieve a similar result by setting the number of places to show after the decimal when setting the data format of the cell. I chose 6, which will produce an eight digit number for values in the range 10 to 99.999999, but only seven digits for number smaller than 10, and extra digits for numbers 100 to 999.999999, etc.

Regards,

Barry

Päääm wrote:

it starts with position 13.

Actually, in the example, the alpha characters start at different positions.

Does the comma count among the first 8 digits?

Is something like this what you want?

SG

Is this all in one cell or is each number in its own individual cell?