Excel formula to display a string of text and numbers

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 |  123456789    |  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? Ideas?

excel gamine

Posted 2009-02-07T04:44:07.523

Reputation:

Answers

 ="prxyz,"&TEXT(A1,"00000")&",0699,ABCD"&IF(LEN(B1)=9,B1*10,B1)&",xxx"

Joel Spolsky

Posted 2009-02-07T04:44:07.523

Reputation: 1 764

i learned that ="prxyz,"& TEXT(A1,"00000")&",0699,"& "ABCD"& LEFT(B1,9)&IF(ISNUMBER( VALUE(RIGHT(B1,1))),"0,xxx",( RIGHT(B1,1)&",xxx")) works too but yours is a lot simpler. :)

I think the last bit with the LEN and b1*10's pretty clever too.

Thanks a lot, Joel! – None – 2009-02-07T06:32:26.633

To do your leading zeroes, you could use the TEXT formula:

="rxyz,"&TEXT(A1,"00000")& ...

I'd have to think about your column B.

Ray Hidayat

Posted 2009-02-07T04:44:07.523

Reputation: 233

very cool! Thanks, Ray!

I hope you can help me out with Column B too wink – None – 2009-02-07T05:15:17.927

There is a way to use a syntax similar to ="prxyz,"&A1&",0699,"&"ABCD"&B1&",xxx" but I would recommend against it. Instead create a data mask somewhere like this (same sheet or another):

 |F     |G|     H    |I|  J  
1|Data Mask:         | |            
2|prxyz,|A|,0699,ABCD|B|,xxx

Once you have the data mask set up use a formula like this:

=$F$2&A1&$H$2&B1&$J$2

Where the $F$2 and so forth are you mask cells and the A1 and B1 are you entered data. That way when you expand the table it will compute automatically.

It appears as though someone beat me to the data validation part. If your still having trouble I can upload a spreadsheet.

EDIT: For column B; If the letter is always the same, it may be better to remove any letters from the number and add them to the next data mask entry. Even if they are different, it may be easier to remove it, and then add it back to ensure you don't miss any.

Phil

Posted 2009-02-07T04:44:07.523

Reputation: 101

Hi, Phil! For Column B, the suffix or letters on the end would be different too..

I don't think I've heard of data mask before. I looked it up in Google and it seems likes it's VBA and I'm not very skilled at it yet. If you could upload a spreadsheet, I'd really appreciate it! – None – 2009-02-07T05:18:16.173

Here is an example: http://philcyr.com/content/ExampleStringFormulation.xls Sorry for the confusion, I don't think that I properly applied the term. I also do not have a solution for the data validation yet.

– Phil – 2009-02-07T05:46:06.243

=CONCATENATE("prxyz,", A1,",","0699",",","ABCD",B2,"xxx");

enter values in A,B column and and then enter the above formula in C column;

next drag the black solid square at bottom-right of selected formula cell downwards

you will get all concatenate cells of A and B with your text and number values.

to get values separately copy the concatenated cells and paste-special in another sheet

select "value" click ok.

Srikanth

Posted 2009-02-07T04:44:07.523

Reputation: