Count Spaces or Find 1st Letter

[Tom@CPC]

I have a dump of data in a worksheet that comes out of "SAP". It is great for a sales rep or a manager to look at because it is a pretty picture of their performance. It is useless as an analytic tool for analysts though. What I need to do is take one column of data from the dump and find the first non-space character. I say non-space character because the SAP application inserts hard spaces that provide indents for a VP to GM to Director to Manager to cost centre reporting hierarchy. The number of spaces indicating the level in the reporting hierarchy. This format is what is useless for analytics.

Any help with some code would be greatful.

There are approximately 350 entries that need to be cycled through. The first non-space character could be anywhere from the second (2nd) to the thirty-first (31st) character. The position of that character should be placed in the cell next to the entry.

For starters, what I've been unsuccessfuly dabbling at is for each cell in the range, look at the first character, and if it is code 160 (for the space) increase a counter by one and move right to the next character and continue increasing the counter until the first non-space is reached. The counter value for the spaces plus one for the non-space value should be returned to the cell beside the active cell. Eventually the Characters excluding those spaces would be entered into columns which will then provide me a rollup structure for these levels.

There will be a pattern for the levels within the hierarchy. For example, first character in position 2 would be a Senior VP, and all first characters in positions 8 thru 10 would be GM's and report to the Sr. VP. Entries whith first characters in position 11 thru 13 would be Directors and report to one of the GM's etc...

Any help would be welcome.

TIA

Tom

 

[IML]

I deal with alot of data out SAP into excel as well (although none of my SAP reports are every pretty). A couple things come to mind:

- try text to column on the column in question

-formulaicly, you want to use
=LEFT(TRIM(A1))
instead of
=LEFT(A1)

or
=LEFT(SUBSTITUTE(A1,CHAR(160),""))

by the way, my blanks are char(32)

 

[Jim North]

CLEAN would take the extra "junk" characters out.

 

[Tom@CPC]

IML,

Thanks!

The second one worked well for extracting the letter, I then applied "find" to it to return the position. I had tried the left and trim functions but it wasn't working for me.

Regards,

Tom

 

[Tom@CPC]

JRN,

I did use Clean to eliminate the hyperlink junk and the expand/contract arrows, but it did nothing for the junk spaces. Thanks for the input.

T

 

[IML]

On 2002-08-21 12:28, Tom@CPC wrote:
IML,

Thanks!

The second one worked well for extracting the letter, I then applied "find" to it to return the position. I had tried the left and trim functions but it wasn't working for me.

Regards,

Tom

glad it worked. If it just the positon you are after, you may be able to get away with

=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(160),""))+1

as well. I misread your question a bit (as is usual for me).

 

[Aladin Akyurek]

Not sure I understood your question properly, but the following would count how many leading spaces an entry has:

=SEARCH(LEFT(TRIM(A1),1),A1)-1

If you also have CHAR(160) before an entry, you could use instead:

=SEARCH(LEFT(TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)),1),A1)-1