Formula to shorten an alphanumeric line of text

[krie2509]

I'm using the formula '=LEFT(A2,4)+0 to shorten a numbered line of text.Is there a way to adopt that formula to do the same thing to an alphanumeric line of text (i.e. 80A0267AA1 to read 80A0267) or even better substitute a "-" in place of the "A" (i.e. 80A0267AA1 to read 80-0267)? I know this is really picky, but I figure it's worth a shot. Thanks

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

I've removed surplus wording this time

[krie2509]

My title is almost the same format as several other posts in this forum. Should I post it as a question?

[royUK]

Your title contains unnecessary wording - "I'm trying to",this is unnecessary in any search terms & makes the Forum Search less efficient

[krie2509]

I appreciate that you are trying to manage and organize the forum efficiently, but I'm spending more time changing my title than receiving any help. My original title was "shorten an alphanumeric line of text" which is nearly identical in format as hundreds of other posts on this forum and I was told this is in violation with RULE 1, but with no explanation how it is in violation. My solution would be to answer the question in the post and then point out how the title should be changed. This would kill two birds with one stone, I would receive the help I'm looking for and I would learn what format is needed for the title. This is just a suggestion, there is a wealth of knowledge on this site and I would like to be able to use it to improve my understanding.

[krie2509]

BUMP I hope I haven't been blackballed

[Ron Coderre]

Do you want replace the first "A" with a dash then eliminate all letters from the value?
Do you want replace the first letter with a dash then eliminate all letters from the value?

Can you tell us a bit more about your starting data?
Will it always be in this general format:
digit digit letter digit digit digit digit letter letter digit
Example:

Please Login or Register  to view this content.

[zbor]

Something like this:

=SUBSTITUTE(LEFT(F2,7),"A","-")

But everything depeding on your input

[zbor]

I've been trying something but didn't make it...

However.. This is where I stuck... How to find first character in a string (A in above case)...

Please Login or Register  to view this content.

Now is the question.. How to take min value from array with #VALUE!
3 is my first non error character...

And it's 3:46 AM... and I think I have enough for today

[DonkeyOte]

How to find first character in a string (A in above case)...

zbor, I think Ron will come up with an elegant solution once we know the full requirements of the OP but in general, to answer your question:

Generally speaking:

Please Login or Register  to view this content.

or using your INDEX construct

Please Login or Register  to view this content.

the same is seen with numerics:

Please Login or Register  to view this content.

but as I say I'm sure Ron has some nice tricks up his sleeve...

[krie2509]

Something like this:

=SUBSTITUTE(LEFT(F2,7),"A","-")

But everything depeding on your input

This worked perfectly. Thank you so much