Count Blank

[saurabh726]

Hi

Is there any formula by which i can count how many blank spaces are there before a text string in a single cell

For ex

help
figure

So if i wanna know how many blank spaces are there before help in single cell and how many blank spaces are there before figure in single cell how do i do that.??

Saurabh

 

[saurabh726]

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.

In this formula the end result im getting is "!value"

whats the reason behind the same??

Saurabh

 

[just_jon]

Not quite following, but you may have non-printing characters comingled with your spaces, especially as this is a Web import. What is you ultimate goal? To purge the data of leading, trailing spaces?

 

[Aladin Akyurek]

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

You have CHAR(160) as leading characters.

In fact you need something different...

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

To add it to your workbook:

Activate Tools|Macro|Visual Basic Editor;
Activate Insert|Module;
Copy the UDF above and paste it in the pane entitled "...(code)".
Activate File|Close and Return to Microsoft Excel.

I'll not comment on the array-formula that you missed to evaluate.

 

[saurabh726]

well i have applied the clean formula it is too not working and my ultimate goal is that im creating a format and applying vlookup however if i extract a report from my software that is java script based some values it gives me trim and some not so in the report that im making im applying three to four vlookups and in all the report its trim except in two reports so now its creating me a problem, coz in vlookup the name is not matching.

so my ultimate goal is to trim these values which is not happening i dont know why and even my number comes in text format and i cant sum them im unable to trace out the problem coz i have applied most of the formulas to it but none of them are getting executed..

all these reports that im extracting are from html which is java script based i mean sql and all

so what im looking is that either i need to contact the progamer who made this program to do the necessary changes or i can apply some of the formulas of deleting the blanks

coz after this formula to extract the value i was applying the right formula which was working only if i edit the text string as i told u

can u suggest me what exactly is the problem..????

Saurabh

 

[Aladin Akyurek]

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.

In this formula the end result im getting is "!value"

whats the reason behind the same??

Saurabh

You forgot CONTROL+SHIFT+ENTER.

 

[Yogi Anand]

Hi Aladin:

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

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.

Hi Aladin:

You are right -- I did realize it after I saw your post. Your solution is the one to go with. It is comprehensive -- since it does not presuppose the non-existence of trailing spaces -- and it is so compact -- Beautiful!

 

[Aladin Akyurek]

Hi Aladin:

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

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.

Hi Aladin:

You are right -- I did realize it after I saw your post. Your solution is the one to go with. It is comprehensive -- since it does not presuppose the non-existence of trailing spaces -- and it is so compact -- Beautiful!

Well, Jon's

=FIND(TRIM(A1),A1)-1

is much better.

 

[saurabh726]

Well, Jon's

=FIND(TRIM(A1),A1)-1

is much better.

ell i have applied the clean formula it is too not working and my ultimate goal is that im creating a format and applying vlookup however if i extract a report from my software that is java script based some values it gives me trim and some not so in the report that im making im applying three to four vlookups and in all the report its trim except in two reports so now its creating me a problem, coz in vlookup the name is not matching.

so my ultimate goal is to trim these values which is not happening i dont know why and even my number comes in text format and i cant sum them im unable to trace out the problem coz i have applied most of the formulas to it but none of them are getting executed..

all these reports that im extracting are from html which is java script based i mean sql and all

so what im looking is that either i need to contact the progamer who made this program to do the necessary changes or i can apply some of the formulas of deleting the blanks

coz after this formula to extract the value i was applying the right formula which was working only if i edit the text string as i told u

can u suggest me what exactly is the problem..????


well its not working still this formula is giving me the result only when i edit the text string orginally its giving me value 0 even if spaces are there.

 

[Aladin Akyurek]

...
ell i have applied the clean formula it is too not working and my ultimate goal is that im creating a format and applying vlookup however if i extract a report from my software that is java script based some values it gives me trim and some not so in the report that im making im applying three to four vlookups and in all the report its trim except in two reports so now its creating me a problem, coz in vlookup the name is not matching. .

Jon's formula pertains to leading spaces.

The array-formula can be made to apply to other chars like CHAR(10) and CHAR(160). But, as already pointed out, use that TrimAll macro to get rid off the unwanted web-related chars.

Addendum: If you don't know how to use TrimAll, here a formula approach...

Book7
	A	B	C	D
1	00234	00234
2	00234	00234
3	00234	00234
4	00234	00234
5	xy606	xy606
6	00234	00234
7	xy606	xy606
8	00234	00234
9	xy606	xy606
10	00234	00234
Sheet4

The formula in B1 is:

=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))

 

[just_jon]

What appear to be spaces are probably not what they seem. If your intent is to have the cell contents be what they "appear" to be, that is remove any leading spaces, trailing spaces and other "garbage" characers, then you can apply the TrimALL function and the CLEAN function -- don't know if the CLEAN function targets characters that TrimALL might miss or not though it should not hurt to apply it.