Hi to all and thanks in advance for your help.
I have a cell with text, which I want to separate. If you pay close attention, the text is essentially two numbers stuck together.
The total number of characters in the cell is either 8 or 9 or 10. This is the basic assumption. Cannot contain less than 8 or more than 10.
Each number that nestles in this text, has DEFINITELY two decimal places. So it can be 1.26 (meaning four characters) or 11.00 (meaning five characters).
As you can see in the table, I have found a solution for 8 and 10 characters.
I have a problem with 9 characters. And the reason is this: Since these numbers (displayed as text) have DEFINITELY two decimal places, it means that they are produced by a number with 4 characters (eg 8.50) and another with five characters (11.00) .
As you can see in the bold text of the table, the number with the four characters may be at the beginning or it may be at the end.
That is my problem. I do not know how to separate it. All I can think is that maybe the secret is in the dot (.). Maybe this is the way to separate them correctly, but I can not express it in the function. Something like: If the cell contains 9 characters and the dot is in the second character (like 8.50) then Left(A4)=4, If dot is in the third character (like 11.75) then Left(A4)=5. And accordingly to the right for the opposite way.
Can somebody help me?
Thanks again in advance
P.S. The new cell like C3, C4, C5, C6, C7, C8 and D3, D4, D5, D6, D7, D8 must be in number format.
I have a cell with text, which I want to separate. If you pay close attention, the text is essentially two numbers stuck together.
The total number of characters in the cell is either 8 or 9 or 10. This is the basic assumption. Cannot contain less than 8 or more than 10.
Each number that nestles in this text, has DEFINITELY two decimal places. So it can be 1.26 (meaning four characters) or 11.00 (meaning five characters).
As you can see in the table, I have found a solution for 8 and 10 characters.
I have a problem with 9 characters. And the reason is this: Since these numbers (displayed as text) have DEFINITELY two decimal places, it means that they are produced by a number with 4 characters (eg 8.50) and another with five characters (11.00) .
As you can see in the bold text of the table, the number with the four characters may be at the beginning or it may be at the end.
That is my problem. I do not know how to separate it. All I can think is that maybe the secret is in the dot (.). Maybe this is the way to separate them correctly, but I can not express it in the function. Something like: If the cell contains 9 characters and the dot is in the second character (like 8.50) then Left(A4)=4, If dot is in the third character (like 11.75) then Left(A4)=5. And accordingly to the right for the opposite way.
Can somebody help me?
Thanks again in advance
P.S. The new cell like C3, C4, C5, C6, C7, C8 and D3, D4, D5, D6, D7, D8 must be in number format.
| A | B | C | D |
| Text | Count Text | Separate 1 | Separate 2 |
| 1.261.13 | 8 | 1.26-->=IF(LEN(A3)=8,LEFT(A3,4),IF(LEN(A3)=10,LEFT(A3,5),"")) | 1.13-->=IF(LEN(A3)=8,RIGHT(A3,4),IF(LEN(A3)=10,RIGHT(A3,5),"")) |
| 8.5011.75 | 9 | 8.50 | 11.75 |
| 4.2021.00 | 9 | 4.20 | 21.00 |
| 11.758.50 | 9 | 11.75 | 8.50 |
| 22.007.50 | 9 | 22.00 | 7.50 |
| 11.0022.00 | 10 | 11.00-->=IF(LEN(A8)=8,LEFT(A8,4),IF(LEN(A8)=10,LEFT(A8,5),"")) | 22.00-->=IF(LEN(A8)=8,RIGHT(A8,4),IF(LEN(A8)=10,RIGHT(A8,5),"")) |
