Question

Locked

Please Help With Excel 2003 Formula

By gaminescholer ·
Hi! I need one column to show the following text:
prxyz,(data1 here),0699,ABCD(data2 here),xxx

data1 is in column A and data2 is in column B.

the format of data1 needs to be 5 numbers (sometimes beginning with zeros).
for data2, they need to type in data that's 9digits long and ends in a letter. if they enter 9 digits only, the letter needs to automatically be substituted with 0 (zero).

so for example i enter '123' in A1, '12345' in A2, '123456789' in B1 and '987654321Z' in B2, it needs to come out as:
A B C
1 00123 123567890 prxyz,00123,0699,ABCD1234567890,xxx
2 12345 987654321Z prxyz,12345,0699,ABCD987654321Z,xxx

I've formatted col A out w custom format 00000 for it to always display 5digits. But I'm stuck on Column B and C.

This is what I've come up with but it's not correct: ="prxyz,"&A1&",0699,"&"ABCD"&B1&",xxx"

Please help?

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

If you aren't actually doing numerical operations

by seanferd In reply to Please Help With Excel 20 ...

just format it as General.

Collapse -

no numerical operations, more of logic sequences

by gaminescholer In reply to If you aren't actually do ...

col C needs to appear in a specific format.
whatever is entered in Col A as Data1 needs to appear as a 5 digit code. If you enter "123" in col A, it needs to appear as "00123" in col C.

same goes with data2 in Col B. Data2 in Col C needs to appear 10 chars long (9 digits + 1 letter, or a 0 'zero' if there's no letter). e.g. If you enter "123456789" it should show as "1234567890" in Col C. if you enter "123456789A" in Col B, it would appear in Col C as "123456789A".

I've learned that by making the Col C formula ="prxyz,"&TEXT(A3,"00000") fixes the first part of my problem. So it's only presenting the data in Col B that's left...

Collapse -

re: formatting

by ThumbsUp2 In reply to no numerical operations, ...

Have you tried using an IF and CONCATENATE in column C instead of trying to format the cell? You can't format the cell with a numeric picture switch if you're going to have an alphabetic character in it.

Collapse -

Problem Solved

by gaminescholer In reply to re: formatting

Yup. I realized that...

Here's the formula I have now that makes it work: ="prxyz,"&TEXT(A1,"00000")&",0699,ABCD"&IF(LEN(B1)=9,B1*10,B1)&",xxx"

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums