Solved

Problems with Concatenation Excel Formula

Posted on 2016-09-21
3
47 Views
Last Modified: 2016-09-21
Hi Guys, I have a problem with a Concatenation Formula in Column J in the attachment.
I received the spread sheet from France and instead of a Concatenation, it is showing "D2439&E2439" instead of the actual value. Any ideas?
20160901_LOB_extract-20160831-mensu.xlsx
0
Comment
Question by:JCutcliffe
3 Comments
 
LVL 29

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41808640
You need to change some settings...

Go to File --> Options --> Advanced --> Scroll down to the section "Display options for this worksheet" and uncheck the box for "Show formulas in cells instead of their calculated results"  --> OK

Now select the whole column J by clicking the column letter and change the format to General and now select the cell J2439 and press F2 (function key) and hit Enter. Now copy the formula down for rest of the cells.
That will resolve your issue.
0
 
LVL 25

Assisted Solution

by:ProfessorJimJam
ProfessorJimJam earned 250 total points
ID: 41808642
it is becuase the cell format is somehow messed up . click into that column and then under the DATA tab click on text to column then the "delimited option should be selected and then click finish and it will fix it.

2016-09-21-14_29_25-Microsoft-Excel-.png
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 41808680
The problem cells are defined as 'text' format, you can change that in the import by selecting the column and ticking 'General' as the format for column J. However the formula does not exist for all rows so it maybe something you added afterwards?
Select column J, right click, format cells, and choose general. You will then need to reenter the formula once (or just edit it (f2) and press return) and then copy it down to correct the rest.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question