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]

To

Tushar
Okay i got it now if i repeat the same it shows me again non visible even after applying the formula


Im mailing you the hard copy for the same

Saurabh

 

[saurabh726]

Well aladin give me email id. i'll email u the hard copy too

Thanx
Saurabh

 

[Aladin Akyurek]

Well aladin give me email id. i'll email u the hard copy too

Thanx
Saurabh

The only thing that I want to know how your entries look like. I don't understand why you don't post just 5 values like in this example:

456
34x5
45
f456
367

 

[saurabh726]

The only thing that I want to know how your entries look like. I don't understand why you don't post just 5 values like in this example:

456
34x5
45
f456
367

i dont have any problem in that but the thing is that if i copy that value out here and then again paste them in excel they just change and they add up and they are just numbers for ex

4
5
6
12
25
214

no text in between

Saurabh

 

[Aladin Akyurek]

The only thing that I want to know how your entries look like. I don't understand why you don't post just 5 values like in this example:

456
34x5
45
f456
367

i dont have any problem in that but the thing is that if i copy that value out here and then again paste them in excel they just change and they add up and they are just numbers for ex

4
5
6
12
25
214

no text in between

Saurabh

Lets try the following:

=--TRIM(SUBSTITUTE(A1,CHAR(160),""))

 

[tusharm]

OK, I give up. You refuse to share what you get even in response to explicit questions. Hopefully, someone else will have the patience to solve your problem.

To

Tushar
Okay i got it now if i repeat the same it shows me again non visible even after applying the formula


Im mailing you the hard copy for the same

Saurabh

 

[TommyGun]

Probably should not butt into this thread (especially with the ppl already involved), but here goes anyway.

Place this code into a module as Aladin described earlier.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Convert2Numbers()
    <SPAN style="color:#00007F">Dim</SPAN> ConversionRange <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> r <SPAN style="color:#00007F">As</SPAN> Range
    
    <SPAN style="color:#00007F">Dim</SPAN> ErrList <SPAN style="color:#00007F">As</SPAN> Worksheet
    
    <SPAN style="color:#00007F">Dim</SPAN> DataProcessed <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> ErrorsFound <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> ConversionRange = Selection
    <SPAN style="color:#00007F">If</SPAN> ConversionRange <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Invalid range selection."
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0

    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>

    <SPAN style="color:#00007F">Set</SPAN> ErrList = Workbooks.Add(xlWorksheet).Sheets(1)

    DataProcessed = ConversionRange.Cells.Count
    ErrorsFound = 0
    
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> r <SPAN style="color:#00007F">In</SPAN> ConversionRange.Cells
        Err.Clear
        
        r.Value = <SPAN style="color:#00007F">CDbl</SPAN>(r.Text)
        
        <SPAN style="color:#00007F">If</SPAN> Err.Number <> 0 <SPAN style="color:#00007F">Then</SPAN>
            ErrorsFound = ErrorsFound + 1
            
            ErrList.Cells(ErrorsFound + 1, 1) = r.Address
            ErrList.Cells(ErrorsFound + 1, 2) = r.Text
            ErrList.Cells(ErrorsFound + 1, 3) = "Unable to convert"
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
    
    ErrList.Range("A1:C1") = Array("Range", "Value", "Desc")
    
    ErrList.Cells(ErrorsFound + 3, 1) = "Errors Found:"
    ErrList.Cells(ErrorsFound + 3, 2) = ErrorsFound
    
    ErrList.Cells(ErrorsFound + 4, 1) = "Values Processed:"
    ErrList.Cells(ErrorsFound + 4, 2) = DataProcessed
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    
    <SPAN style="color:#00007F">Set</SPAN> ErrList = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
Now, select the cells that contain the "invalid" numbers. Run the code, and then copy the results of the new spreadsheet, and paste here.

 

[tusharm]

Yeah, the OP probably only needs to combine SUBSTITUTE (to get rid of 160) with CLEAN (to get rid of other junk) and then convert to a number with 0+, 1*, or --

Probably should not butt into this thread (especially with the ppl already involved), but here goes anyway.

Place this code into a module as Aladin described earlier.

{snip}

 

[TommyGun]

Agreed.

 

[saurabh726]

Well thanx a lot for the forumla aladin it worked u had been a gr8 help and thanx topgun also

well tushar if i didnt wanted help then i wont have mailed u the format i actually mailed u the format from my email id

Thanx A LOT GUYS

Saurabh