Please use FIND function to locate the spaces in a text string.
1. To locate the first space:
=FIND(" ",Cell), one space is included
Explanations:
– " ": Space is included in the citation sign;
– The cell is required which contains the text string.
2. To locate the second space:
=FIND(" ", Cell, FIND(" ",Cell)+1)
Explanations:
– FIND(" ", Cell): To find the location of the first space;
– FIND(" ", Cell, FIND(" ",Cell)+1): To find the location of the second space based on the first space location.
3. To locate the third space:
=FIND(" ",Cell, FIND(" ",Cell, FIND(" ",Cell)+1)+1)
Explanations:
– FIND(" ", Cell): To find the location of the first space;
– FIND(" ", Cell, FIND(" ",Cell)+1): To find the location of the second space based on the first space location;
– FIND(" ",Cell, FIND(" ",Cell,FIND(" ",Cell)+1)+1): To find the third space location based on the first two spaces.
4. To locate the fourth space:
=FIND(" ",Cell, FIND(" ",Cell, FIND(" ",Cell, FIND(" ",Cell)+1)+1)+1)
Explanations:
– FIND(" ", Cell): To find the location of the first space;
– FIND(" ", Cell, FIND(" ",Cell)+1): To find the location of the second space based on the first space location;
– FIND(" ",Cell, FIND(" ",Cell,FIND(" ",Cell)+1)+1): To find the third space location based on the first two spaces;
– FIND(" ",Cell,FIND(" ",Cell, FIND(" ",Cell,FIND(" ",Cell)+1)+1)+1): To find the fourth space location based on the first three spaces.
Please note that when there are not enough spaces in the text string that you have specified, the formula will return the #VALUE! error.
