Clean the start of string only

[YasserKhalil]

Hello everyone
I have UDF of Mr. Karedog that cleans the end of specific string only

Please Login or Register  to view this content.

Now I need the vice versa .. to clean the start of the string only
Would I reverse the loop ..? I am confused about that

[Kaper]

Hi,
It seems that the presented function does check not every character near the end, but only every second one.

Is it also the case in checking front?

Anyway, may be at the beginning have a look on the following:

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot for help but it doesn't seem to do the trick as I can't figure pairs of characters are to be removed.. I just need to clean any starting characters that are unusual such as chr(32) chr(160) .. hope this helps

[Kaper]

Have you tried the code? As it is now it shall do the work. If char(160) is also concerned, it can be added to case statement:

Please Login or Register  to view this content.

PS. Have you tried built-in function LTrim?

[YasserKhalil]

Thanks a lot Mr. Kaper for great help
I have tested LTrim and doesn't work for me
As for the new UDF it is working but I need to make the number in this mid(param,2) variable (it is unknown for me) ...and that's the problem now
Hope it is clear ..Let's try to stop when valid character (small or capital letters encountered ..)

I have attached a file with one example ..

[karedog]

Maybe :

Please Login or Register  to view this content.

[YasserKhalil]

That's Awesome & fascinating. Thanks a lot for great help Mr. Karedog
Best and kind regards

[karedog]

You are welcome.

Regards

[daboho]

Sir karedog what meaning param its array

[karedog]

VBA can automatically convert a string to array of bytes.
You can examine this example :

Please Login or Register  to view this content.

[daboho]

Is mean convert string to large number array (as byte)

[karedog]

Yes, the array's size is twice the length of string.

[daboho]

Ok thankyou sir karedog.

[daboho]

sir karedog what mean Case 0 To 32, 127, 129, 141, 143, 144, 157 this is char$ for removing text and Mid$(param, (i + 2) \ 2) is mean if i = 0+2

Please Login or Register  to view this content.

[karedog]

what mean Case 0 To 32, 127, 129, 141, 143, 144, 157

This thread is actually continuation of http://www.excelforum.com/excel-prog...t-the-end.html
There is a link in the code that point to Microsoft's site that explain how the clean() function works. ( https://support.office.com/en-us/art...5-4b8ba987ba41 )
In this site, you can see the number 0 To 32, 127, 129, 141, 143, 144, 157.

this is char$ for removing text and Mid$(param, (i + 2) \ 2) is mean if i = 0+2

Lets try this example :

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The code loop from 0 (LBound(b)) to 21 (UBound(b))
When i = 0 --> b(i) = 32 --> included for "Case 0 To 32, 127, 129, 141, 143, 144, 157" so proceed to next i
When i = 2 --> b(i) = 32 --> included for "Case 0 To 32, 127, 129, 141, 143, 144, 157" so proceed to next i
When i = 4 --> b(i) = 76 --> VBA found first non blank letter so terminate the For..Next loop

To locate the position in the string (not the array), this formula is used :
(i + 2) \ 2
If you substitue i with 4, you will get (4 + 2) \ 2 = 6 \ 2 = 3 --> as you see the "L" is the third character of string " LLO WORLD"

[daboho]

Thankyou karedog