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]

Thanx a lot alaydin ur last formula works

hey u can help me in one more thing

i got some numbers which are showing in text format i mean its not getting sum when im adding them is there any way to add them up again they have been extracted from the same source

Saurabh

 

[just_jon]

If you need assistance in running the macro, post back; Aladin's revision to the array formula to purge CHAR(160) should do essentially he same thing.

EDIT: For your last problem, try highlighting the column then click Data | Text to Columns | Finished.
This should re-set the underlying format of the column to General.

 

[Aladin Akyurek]

Thanx a lot alaydin ur last formula works

hey u can help me in one more thing

i got some numbers which are showing in text format i mean its not getting sum when im adding them is there any way to add them up again they have been extracted from the same source

Saurabh

Are they all numbers what you deal with?

 

[saurabh726]

well they are numbers and bascially they are the query type of calls that a agent took but they have been exported from java script of the same software and on formula =istext(No)= True like it saying true and coz of that its not getting add and dont know how to add them up.

Saurabh

 

[Aladin Akyurek]

well they are numbers and bascially they are the query type of calls that a agent took but they have been exported from java script of the same software and on formula =istext(No)= True like it saying true and coz of that its not getting add and dont know how to add them up.

Saurabh

Change tthe Trim formula to:

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

 

[saurabh726]

Change tthe Trim formula to:

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

It gives me end result in =!value

Its not getting calculated

Saurabh

 

[just_jon]

You will get #VALUE! if the data in the cell is not reducable to a numeric, such as help3. Can you 1] Tell us what your eyes tell you is in the cell in question, and 2] Tell us what value is returned by =LEN(A1) assuming cell A1 is the cell which is giving you the problem.

 

[Aladin Akyurek]

Change tthe Trim formula to:

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

It gives me end result in =!value

Its not getting calculated

Saurabh

It's about time that you post a sample of 10 problematic values.

 

[saurabh726]

In len it shows value 2 if the value is there 1 and on blank it shows 2 i didnt understand the first part what ur were asking for jon..??

and alayudin dont know how to insert values out here..can u help me???

anyways going out for 3 days will be back by the mean time please search for a formula it will be of great help to me.

and i want to thank you jon and aladin u guys had been of gr8 help coz of u guys my format is almost finalised just waiting for these values to get add up

will be back after three days

thanx for your help

Saurabh

 

[just_jon]

You can download Colo's free utility at the bottom of this screen. Once installed, you can take a screenshot of your spreadsheet and load it in a post.

What I was looking for was like this: In cell A1 I see what loks like 123 but it does not act like a number in that it sdoesn't sum. When I do =LEN(A1) the value returned is 4...