How to get blinking text in Excel

Excel formulas - separate number or text (right)

Would you like to read the last part of a string in Excel, which is either a number or a text, but you don't know how to do it? Please see this article for help.

Here I have already shown you how you can read out the first part of a string, which is also either a text or a number. This article is basically the counterpart to it. He shows how you can read out the rear part with two formulas.

content

Text number:

If you have a cell that is structured in the "TextZahl" format and you want to read the number, you need a certain formula. But first of all, to build up in cell A1, write a string in the format “TextNumber”, such as “abc123”. In B1 comes the important part, the formula:

= PART (A1, COMPARE (TRUE, ISNUMBER (PART (A1, COLUMN (1: 1), 1) * 1), 0), LENGTH (A1)) * 1

Since this formula is again a matrix formula, the confirmation of entry is not sufficient. So first click again in the formula edit field. Then press the CTRL + SHIFT + Enter keys to confirm the formula and "convert" it to the matrix formula, which looks like this:

{= PART (A1; COMPARE (TRUE; ISNUMBER (PART (A1; COLUMN (1: 1); 1) * 1); 0); LENGTH (A1)) * 1}

The "number" is now output, if you have chosen my example, it is "123".

(Click on the images to see them in original size)

Text decimal point:

Now I'll show you what happens when you write a text in A1 and a decimal point separated by a space. Please leave cell B1 unchanged and only change the format of A1. Now turn “TextNumber” into “Text Float”. It can look like this, for example: "Test 15.750". The display in B1 will now change and show the decimal point. If, like me, you have chosen a "0" as the last digit of the decimal point, this will have disappeared as it does not determine a more precise value. In my example, “15.75” was displayed here.

Text time:

This formula can also work wonderfully with a time or an hour. If you change A1 to the format "Text time", so that you write, for example, "Time: 12:00", cell B1 will react. Instead of just writing the number "12:00", the program knows that the hour is being used here. And since the program regards 24 hours as "1", 12 hours logically result in the value "0.5", as in my example.

Number text:

This format is practically reversed in example 1. Since these instructions are about how to read out the last part of a string consisting of a number and a text, the text is logically displayed here. Now write a string in cell A1 which has the structure “number text”. I chose "123abc" for this. Since a text is output here, the associated formula must of course also change. Now delete the current formula in B1 and enter the following new matrix formula:

= PART (A1, MAX (ISNUMBER (PART (A1, COLUMN (1: 1); 1) * 1) * COLUMN (1: 1)) + 1; 99)

As already explained a little further above, this formula must now be confirmed again with CTRL + SHIFT + Enter.

Now you know how to get the last part of a string. With these other two instructions here and here you can now read everything out, whether in front, in the back or in the middle.