Home » Questions » Computers [ Ask a new question ]

Excel formula to display a string of text and numbers

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

Asked by: Guest | Views: 76
Total answers/comments: 1
Guest [Entry]

"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."