• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Removing letters the beginning of a value

A

ann99m

New Member
Need to get rid of any letter(s) at the beginning of each value. Problem is, the values vary in character length.


Any thoughts? Thank you!


S1021WA

S1021WC

S1021WD

S1023

S1024

S1052

S1054

S1056

S1059

S1134NC

S1135NC

S1136NC

S1137NC

S1138NC

S1139NC

S1172

S1173

S1174
 
LEN() returns the number of characters in a text string,

not the length of a cell
 
Hi, ann99m!

If your equipment has as default language one that uses DBCS (double-byte character set), then to retrieve the actual number of characters in a cell, you should use LENB instead of LEN. If LEN(A1) retrieves 4, LENB(A1) will retrieve 8, in DBCS; in other configurations both functions retrieve 4.

Regards!
 
Was just looking at the formula more, and won't this just take 1 character off the data? I need 1 or 2 characters, the letters at the beginning need to be removed, sometimes there is one, sometimes two.
 
Hi ,


What about the letters towards the end of the text ? Can they remain or do you want that they should also be removed ?


Narayan
 
they can remain. I am just going through and first removing the first letter if any, and then removing the second letter if any. almost done, not too much data thank goodness.
 
This array formula should trim off any amount of letters before the first number:

=MID(A2,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2))),999)


If no number is found, formula will give #VALUE! error.

Remember that array formulas need to be confirmed using Ctrl+Shift+Enter.
 
Yes, Hui. you are right. it's the number of character, not length. it's just my way of remembering that function. The more characters it has in a cell the longer, hence length, it looks to me.


;)
 
@oldchippy,

Well, whaddayaknow, it does! Didn't realize XL could handle a natural array like that. Thanks for the tip!
 
You must log in or register to reply here.
Back
Top