LEFT, MID, and RIGHT = text and/or number

[OutCold]

Hello, first time user, first post

I have a 2 page workbook where sheet2 is a large chart which gives different definitions for myriad alphanumeric codes. My goal is to give a user of this workbook the ability to enter a 6 digit, alphanumeric code into one cell on sheet1. In 6 separate cells on sheet1 I'm using the LEFT, MID, and RIGHT functions to pull each digit from that code. Each digit has the potential of being a number or text. Also, each of the six digits have a different definition. In a 7th cell on sheet1 is where I have my vlookups pulling info from sheet2.

My workbook works perfectly when only using the 6 cells to enter data. It's only when I use the left, mid and right functions that I get the error "#N/A". Furthermore, it only gives the error when the digit to be shown is a number, obviously because it's formatted as text. The problem is, sometimes it WILL be text.

A1 will have 6 digit alphanumeric code
A2 will show value of digit 1 only.
A3 will show value of digit 2 only. etc...

How can I format my cells, that will inevitably have a Number value, to automatically format themselves for numbers or text?

Thanks,
Rich

 

[azumi]

What the data looks like?

 

[Marcelo Branco]

Hello, first time user, first post

I have a 2 page workbook where sheet2 is a large chart which gives different definitions for myriad alphanumeric codes. My goal is to give a user of this workbook the ability to enter a 6 digit, alphanumeric code into one cell on sheet1. In 6 separate cells on sheet1 I'm using the LEFT, MID, and RIGHT functions to pull each digit from that code. Each digit has the potential of being a number or text. Also, each of the six digits have a different definition. In a 7th cell on sheet1 is where I have my vlookups pulling info from sheet2.

My workbook works perfectly when only using the 6 cells to enter data. It's only when I use the left, mid and right functions that I get the error "#N/A". Furthermore, it only gives the error when the digit to be shown is a number, obviously because it's formatted as text. The problem is, sometimes it WILL be text.

A1 will have 6 digit alphanumeric code
A2 will show value of digit 1 only.
A3 will show value of digit 2 only. etc...

How can I format my cells, that will inevitably have a Number value, to automatically format themselves for numbers or text?

Thanks,
Rich

Maybe something like this


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

AN24G7​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

A​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

N​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

7​

[/TD]
[/TR]
</TBODY>[/TABLE]



Formula in A2 copied down
=IFERROR(0+MID($A$1,ROWS(A$2:A2),1),MID($A$1,ROWS(A$2:A2),1))

Hope this helps

M.

 

[Daveydoodles]

You'll need to use =value(

so something like =value(left(A1,1))

That will return the number as a number. To account for times when it is supposed to be text, use something like =iferror(

So =iferror(value(left(A1,1)),left(A1,1)) will return the first character or A1 as a number when it's a number and as text when it's not.

 

[OutCold]

you'll need to use =value(

so something like =value(left(a1,1))

that will return the number as a number. To account for times when it is supposed to be text, use something like =iferror(

so =iferror(value(left(a1,1)),left(a1,1)) will return the first character or a1 as a number when it's a number and as text when it's not.

this worked perfectly!!!! Thanks!!!!!