Find the first character in a cell

[Mindpsyche]

Hello,

If I have a cell which begins with a few spaces and then has a character, how do I use the find function to see how many spaces until some character is present in the cell. The * does not work with this.

Excel 2007

	B
4	a

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[JackDanIce]

Not sure if this is what you want, but you can try, the following which works without using the FIND function:

=LEN(A1)-TRIM(LEN(A1))

This will count the number of spaces at the start until a non-space character is reached

 

[Mindpsyche]

Super thanks, exactly what I needed.

 

[Aladin Akyurek]

Hello,

If I have a cell which begins with a few spaces and then has a character, how do I use the find function to see how many spaces until some character is present in the cell. The * does not work with this.

Excel 2007

	B
4	a

<tbody>
</tbody>

Sheet1​

=FIND(LEFT(TRIM(B2),1),B2)

returns the position of the non-space char.

=FIND(LEFT(TRIM(B2),1),B2)-1

gives the number of spaces to the first non-space char.

 

[Aladin Akyurek]

Not sure if this is what you want, but you can try, the following which works without using the FIND function:

=LEN(A1)-TRIM(LEN(A1))

This will count the number of spaces at the start until a non-space character is reached

Trailing and extraneous in-between spaces would thwart this formula completely.

 

[JackDanIce]

Thanks Aladin, wasn't aware of that

 

[Mindpsyche]

Thanks Aladin