Concatenate or Text on Text fields

Ducati_John

New Member
Joined
Mar 15, 2006
Messages
12
Hi

I have a spreadsheet that was designed to convert a 318 character string into appropriate fields.
To preserve the original format and any line spaces this data is ALL save as TEXT

I am attempting to now go backwards and convert this data back into its original single 318 character field. My issue is that when using either the concatenate or text function, it treats the formula as TEXT instead of a formula.

=concatenate(A2,B2,C2,D2,E2,F2,G2,H2,I2,J2,K2,L2,M2,N2,O2,P2,Q2,R2,S2,T2,U2,V2,W2,X2,Y2,Z2,AA2,AB2,AC2,AD2)

Have attempted to reformat this field into all the different options but with no success.

I suspect the issue is related to the data that is stored as TEXT?

Any assistance would be appreciated.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Joined
Jul 30, 2006
Messages
3,656
Assuming that the original text string was 'comma' separated, copy the below formula to a CELL in your spreadsheet:

=CONCATENATE(A2&",",B2&",",C2&",",D2&",",E2&",",F2&",",G2&",",H2&",",I2&",",J2&",",K2&",",L2&",",M2&",",N2&",",O2&",",P2&",",Q2&",",R2&",",S2&",",T2&",",U2&",",V2&",",W2&",",X2&",",Y2&",",Z2&",",AA2&",",AB2&",",AC2&",",AD2&",")

Then copy and Edit, Paste Special, Values, the CELL back onto itself.

Hope this helps.


Have a great day,
Stan
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
John

Reformat the cell to General, then press F2, enter.

That should make it show a result, not treat it as text. Basically, you have to format the cell back to general before you try to enter a formula. If you do it afterwards, then you have to edit the formula, and put it back in again.

HTH

Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,114,676
Messages
5,549,371
Members
410,911
Latest member
AniEx
Top