Count Blank

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Dec 14, 2003

• #2

Will there be any trailing spaces?

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Dec 14, 2003

• #3

Hi Saurabh:

It is not clear from your description what you are trying to accomplish. In the example that you have given in your post, can you elaborate what you are getting at, and what you think is the correct answer -- and then let us take it from there.

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Dec 14, 2003

• #4

Book7
	A	B	C	D
1	help	3
2	figure	2
3	helpfile	2
4	saturn	#N/A
5	vtacdzxsa	1
6
Sheet1

The formula in B1 is:

=MATCH(" ",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

which must be confirmed with control+shift+enter instead of just with enter.

An #N/A result means no leading space.

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Dec 14, 2003

• #5

Is this what you are looking for ...

y031214h1.xls
	A	B	C	D
1	help	3
2	figure	1
Sheet2

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Dec 14, 2003

• #6

Yogi Anand said:

Is this what you are looking for ...

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

...

Click to expand...

Now you're hoping that there are no trailing spaces, which obviously cannot be guaranteed.

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Dec 14, 2003

• #7

Hi Aladin:

You are right -- my formulation assumed only leading spaces (what the OP called 'before the word')!

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Dec 14, 2003

• #8

How about --

Book1
	A	B	C	D
1	help3	3
2	spaces2	2
3	nonehere	0
4	hereis1	1
5	nonehere	0
6	twohere	2
Sheet1

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Dec 14, 2003

• #9

Yogi Anand said:

Hi Aladin:

You are right -- my formulation assumed only leading spaces (what the OP called 'before the word')!

Click to expand...

You seem to miss the point. OP might not realize the point which motivates Jon's question, I'd expect that you should. If you didn't, you might have realized it after the formula I posted.

 

Upvote 0

S

saurabh726

Well-known Member

Joined

Dec 13, 2003

Messages

891

• Dec 14, 2003

• #10

Thanx A LOT YOGI AND jUST_jon

well both the fomulas are working if i edit the text string they dont work orginally

i have exported this file from a html which is based on java script does that makes any difference or something else..coz if i edit this string manually i mean spaces they work otherwise they are not working.

Please suggest what exactly the problem can be

Saurabh

 

Upvote 0