Results 1 to 15 of 19
Thread: Excel formula prob (2003)

20061127, 20:45 #1
 Join Date
 Jul 2002
 Location
 Dallas, Texas, USA
 Posts
 43
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excel formula prob (2003)
I'm having a problem with CONCATENATE that I also have sometimes with VLOOKUP. The formula result I'm getting in cell D25 is =CONCATENATE(B25,C25) instead of a combination of the contents of the two cells. Anybody know why this is and what I can do about it?

20061127, 20:55 #2
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Excel formula prob (2003)
Hi Rachel,
Not without seeing it but I have a theory that you may be trying to concatenate a numerical/date with a text string.
What are you trying to use?Jerry

20061127, 20:56 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Excel formula prob (2003)
If cell D25 has been formatted as Text (in the Number tab of Format  Cells...), any formula you enter into the cell will be interpreted as plain text.

20061127, 21:09 #4
 Join Date
 Jul 2002
 Location
 Dallas, Texas, USA
 Posts
 43
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel formula prob (2003)
Hi, Jezza and Hans. Here's the crazy thing I'm trying to do (and I do it frequently with concatenate). Column C is a 10 digit number currently formatted as text (I tried formatting it as general, and it doesn't make any difference as to the concatenation result). In column B I have inserted a single apostrophe and copied down a few thousand cells (because it's really the whole column of numbers © I'm having problems with). I'm trying to concatenate the single apostrophe and the list to format the numbers so that I can use VLOOKUP to match what I've concatenanted (after copying and saving as values) with data download from an AS400 system.
Normally I would convert the numbers I downloaded to text and then do my vlooking up with excel data; however, the table with my AS400 data already has a bunch of other analysis in it, so I dare not "reformat" those numbers. I'm sure there's some technical term for this process (and maybe an easier way to do it), but all I know is I need the concatenate formula to work so that I can then get the VLOOKUP to work.
Sometimes the concatenate DOES work in this situation, so it's really weird when it doesn't.
Thanx for your responses.
Rachel

20061127, 21:10 #5
 Join Date
 Jul 2002
 Location
 Dallas, Texas, USA
 Posts
 43
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel formula prob (2003)
Column D is formatted as general (I've tried reformatting it to text and back again, but it doesn't help). Thanx. Hans.

20061127, 21:17 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Excel formula prob (2003)
Can you post a small sample workbook demonstrating the problem?

20061127, 21:18 #7
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Excel formula prob (2003)
<img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> Can I see an example, the explanation is rather confusing
Jerry

20061127, 21:40 #8
 Join Date
 Jul 2002
 Location
 Dallas, Texas, USA
 Posts
 43
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel formula prob (2003)
Jerry and Hans, unfortunately (for purposes of an example) I can't provide an example because I just fixed the problem by going back to the downloaded data whose results I need to use in the VLOOKUP and converting it to general after ensuring I wouldn't mess up any analysis I'd already done by doing so.
It's a common problem associated with using AS400 numeric data that's in an AS400 alpha field. Generally, we get in the habit of converting those fields to general by doing a text to columns on it (simple reformat doesn't work) before we can use VLOOKUP. This time I needed to do it backward and convert the general (or text, doesn't matter) excel data to AS400 (by adding an apostrophe to the beginning of the numeric datawe do this all the time before uploading from Excel to the AS400). The easy was is to insert a column before the datafield and copy down a single apostrophe and insert a column after the datafield, inserting the concatenate function and dragging it down. It almost always works. Today it didn't.
Thank you for your quick responses. I would have been delighted to provide a sample, but I already killed it.
Rachel

20061127, 21:43 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Excel formula prob (2003)
Never mind  I'm glad the problem is fixed!

20061127, 21:48 #10
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Excel formula prob (2003)
Hi Rachel
The reason I am interested as a good part of my life was taken up a few years back dealing with legacy data and I came a resident expert in finding methods to get around these problems. A lot of the issues (I am sorry, I do not know what AS400 is but i am assuming that it is an accountancy package) surround the fact that these systems place rogue characters in the extract which Excel cannot read or it creates a hidden character.
I have found that a simple routing of Importing the data through Excel from the extract can get rid of a lot of the issues you describes , especially when converting to text during the import instead of trying to change it in situ.
Can I suggest in future posts that you provide a small sample piece of data for us to look at. I find, along with others that a sample is so much more easier to fix <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
BTW Glad you got it fixedJerry

20061127, 21:52 #11
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel formula prob (2003)
When you formatted the cell to Text and back to General, did you then reenter the formula? Once the formula is entered as text, it will not revert back to a formula without being reentered. You can do this easily by selecting the cell, press F2, then press Enter.
Legare Coleman

20061127, 22:50 #12
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel formula prob (2003)
I would use the TEXT function to convert the numeric values to text values rather that putting the single quote in front of the value. If you use the TEXT function, then you can use the format string parameter to insure that the text strings are consistent. If the values are in column A, then in an empty column you could enter a formula like this:
<code>
=IF(ISTEXT(A1),RIGHT("0000"&A1,4),TEXT(A1,"0000"))
</code>
The formula above is for values/strings of up to four digits in column A. The formula would have to be adjusted depending on what your data looks like.
and fill it down as far as necessary. Then you could format column A as text and then copy the column where you put the formulas and use "Paste Special"/Values to paste the text values back into column A and delete the column with the formulas.Legare Coleman

20061127, 22:58 #13
 Join Date
 Feb 2002
 Location
 A Magic Forest in Deepest, Darkest Kent
 Posts
 5,681
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Excel formula prob (2003)
Legare
Another little trick I used to use and as Rachel is talking about VLookup and numerical values was to use:
=VLOOKUP(A2+0,$BB1:$BC100,2,FALSE)
I added the zero to convert the text value in A2 to a numeric value and that negated the requirement to convert to text values. The formula above is purely an example to demonstrate how I did it.Jerry

20061127, 23:16 #14
 Join Date
 Jul 2002
 Location
 Dallas, Texas, USA
 Posts
 43
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel formula prob (2003)
Jerry, thanx for your response. Yes, I should have held off before converting my data so I could supply a sample. It's been a couple of years since I've posted here, and I had forgotten people frequently need a sample. The rest of this is about what I was trying to do and using AS400 data in Excel.
AS400 is an old (but still in use) IBM platform that supports our accounting package resides (I believe the language is RPG, but I'm not sure). The data import is done through ODBC linking the AS400 directly to Excel, so the data comes straight in to Excel via an IBM product which is an Excel AddIn  iSeries Access Data Transfer. Therefore, I can't do the routing you spoke of.
But you're right about the hidden character thing. I'm trying to match well numbers associated with data transferred from the AS400 to well numbers in a data table created in Excel. The problem is that the well # in this program is in an alphanumeric field that is populated with numeric data. As a result, to convert data in those fields to upload to the AS400, we have to add an apostrophe before the first character which basically makes it a text value that the AS400 will accept in that field.
In downloading the data, there is a character that Excel doesn't see, and it's that apostrophe. It's easily eliminated, but it can be eliminated in only one way  that is to select the column of data and do a text to columns, selecting "general" for the format. It removes the apostrophe and makes the data numeric (and doesn't even move the data to another column). (One would think that the column could be selected and a search/replace done for the apostrophe, but the search function can't see it  interestingly, once concatenated, the naked eye can't see it in the formula field either, even after the formula is converted to a value. What can be seen is a green triangle in the upper leftmost corner of each cell containing that datatype. )
Usually, I don't bother with the text to column reformat because I'm downloading and comparing different data tables from the same AS400 system, so VLOOKUP and CONCATENATE work fine because it's an apples to apples comparison of data types. Late in my analysis, I realized I needed to pull in some data from an Excel table, but the well #'s were not in the AS400 format. I therefore tried to convert the Excel well #'s to AS400 format by doing the same thing I do when I create a table to upload into the AS400 system  insert a column before and after the well #, insert an apostrophe in the field preceding the well # and the CONCATENATE formula in the empty column after the well #. This has always (I thought) worked for me. I then end up with a result in the concatenated field that looks like AS400 data. Once I select that column and do a copy/paste special/values, I end up with a well number formatted exactly as it was in the AS400 and I can then go forward with VLOOKUP.
So, I hope I've provided a clear explanation. There's a whole underworld of us AS400 users with tips and tricks one how to use Excel formulas to work miracles on AS400 data.
Thank you again.
Rachel

20061127, 23:35 #15
 Join Date
 Jul 2002
 Location
 Dallas, Texas, USA
 Posts
 43
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel formula prob (2003)
Legare, your trick of adding the +0 to the VLOOKUP formula sounds like an excellent one. I will give it a try next time I run into this problem (which will most likely be tomorrow). Thank you. Thank you also for your other suggestion. I don't think it will work because of the apostrophe, but I will probably give it a shot anyway.
Thank you.
Rachel