Solved

Problems with Concatenation Excel Formula

Posted on 2016-09-21
3
48 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 30

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 26

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel if statement 3 15
Distribute Values over date range 15 36
Do Wend Macro not working 22 33
Conditional Vlookup to take certain range of data only 6 14
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

839 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