Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Excel 2003 Please Help With CONCAT Dilemma [Solved/Closed]

Report
-
 excelgamine -
Hello,
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 if 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?

(I'm clueless about macros so I'd really appreciate it if I can do this just using Format and Functions.. I hope this is possible?)

1 reply

Answer 1 / 1
Posts
7096
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
February 22, 2021
 483
Hi,
Try this formula : ="prxyz,"&A1&",0699,"&"ABCD"&IF(LEN(B1)=9; B1&"0";B1)&",xxx"
Concerning the column A format, you could also use the format text.
Best regards
Report

Hi!

It's showing up a formula error... but I've found a formula that works though! Here it is:
="prxyz,"&TEXT(A1,"00000")&",0699,ABCD"&IF(LEN(B1)=9,B1*10,B1)&",xxx"

:)
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!