Solved

Problems with Concatenation Excel Formula

Posted on 2016-09-21
3
39 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 28

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now