Solved

How to replace parse formulas with absolute Values

Posted on 2014-12-04
34
60 Views
Last Modified: 2015-01-22
Hi,

I separated mixed numeric and alphabetic date into two separate columns.   However the data in the new columns remains the formula used for the separation and not the value of the data.  How do I convert the new columns from the formula to the data the formula displays.   I need to delete the original column to combine the worksheet into a master worksheet.
I lose the data in the new columns to a missing formula error when I try to delete the column with the mixed data.

45 Barclay Avenue  

 =REPLACE(Y4,1,FIND(" ",Y4),"")    produces  Barclay Avenue

   =LEFT(Y11,FIND(" ",Y11)-1)  produces 45

thanks,
howie
0
Comment
Question by:Howie_Lynn
  • 14
  • 10
  • 6
  • +2
34 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40481281
Copy the column and the use paste special and select Values. That will paste only the results.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40481304
Menu:  Home, Clipboard group, Paste, Paste Special...

Here's what the Paste Special dialog looks like:
paste special values
-Glenn
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40481339
Yes like I already said, Use PASTE SPECIAL, VALUES!
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40482637
Or use the Text to Columns function to split the data in place rather than using formulas.

Thanks
Rob H
0
 
LVL 2

Author Comment

by:Howie_Lynn
ID: 40483621
When I choose formulas Excel increments the column reference and the function is correct,  when I choose value Excel does not increment the column reference but copies the values from the first column where the formula is located and the function is not correct.
0
 
LVL 2

Author Comment

by:Howie_Lynn
ID: 40483628
Also, I would like to name these functions so they can be easily accessed.
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40483762
If you copy a column and then choose paste special-Values, It will increment NOTHING it will just paste the values and thats what you asked for.
0
 
LVL 2

Author Comment

by:Howie_Lynn
ID: 40484504
Windows 7 home, desktop mini server.   Latest version of Excel.    As you already know, when dealing with Microsoft software, running on  third party hardware,  on third party servers, and third party virus protection,  there has got to be inconsistencies.   You know those strange things that we assume is the users fault?   Well they may not be as simple as we sometimes think.   With all due apologies to your excellent staff and service to the Microsoft community,  please don't give Microsoft credit, where credit is no due.  

When, I copy my purge formula, using a copy and paste from one cell to several adjacent cells,  using special  paste value option. it does in fact copy the value from the original cell into the target cells.  Perhaps not on your system,  but I'm  running on my system.  And strange as that may seem, that is the way it is working.   And since I worked for Lockheed Martin for thirty years as a systems engineer, I have personally experienced a lot of very strange software anomalies both in the Apple and Microsoft world.  But a lot less in the Apple world,  since their software is running on Apple hardware.  Your think?

But I did discover if I copy a range of cells produced with a simple paste option containing the formula and displaying the correct values,  I discovered this.

If I paste the original column, using a paste value option, into a new column, the new column contains only the values not the formula and values.  

The text to column option also works well, with a view problems of split text due to spacing.

But thanks to each of you for your help.  Together we have a solution.    And that is what i important.

Thanks,
Howie
0
 
LVL 2

Author Comment

by:Howie_Lynn
ID: 40484737
I've requested that this question be closed as follows:

Accepted answer: 0 points for Howie_Lynn's comment #a40484504
Assisted answer: 41 points for Neilsr's comment #a40481281
Assisted answer: 41 points for Glenn Ray's comment #a40481304
Assisted answer: 41 points for Rob Henson's comment #a40482637

for the following reason:

Because my worked, with proper explanation.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40484738
As one of the others said, your question suggested you needed to convert to values; that's what all of the suggestions have given you. When you paste, you will paste either values or formulas, not both.
0
 
LVL 2

Author Comment

by:Howie_Lynn
ID: 40491095
My comment was confusing.   I should have said "when I copy the parse formula into a range of cells using the paste special options "value" the "formula" is pasted into the target cells and the "value" is displayed on top of the formula.   The value is correct and it appears as if the desired result is correct.  This is not apparent until I delete the original cells with the combined data.  Then in my new cell a formula error is generated indicating the original source is missing.  I solved this problem by coping the new range of cells, with both the value and underling formula, into an additional  new range of cells using the paste special options value option.  This new range of cells contains  only the values not the value and formula combined.  I then could delete the unwanted range of cells without error.  My final new  range of cells displayed the correct values.
This spreadsheet contains 5000 records and unfortunately a range of cells containing both text and numeric date.  Which cannot be sorted by Excel.  
This is complicated to explain and probably more complicated to read and understand.   But this is a solution to a problem with Excel which your user community might find helpful.  
Howie
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40491114
There is option to Paste Special Values and get BOTH the Formula AND the Value in a cell.  

Paste Special Values will ONLY ever paste the actual value of the cells as of the time you do the copy paste.  Any change to any cell after that will never affect those cells that you Pasted special.

And that is what I stated you needed to do in the very first post.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40491229
Can you clarify what you mean when you say

"value" is displayed on top of the formula

Thanks
Rob H
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40491252
Sorry my comment "There is option to Paste Special Values and get BOTH the Formula AND the Value in a cell."

should have said...

There is NO option to Paste Special Values and get BOTH the Formula AND the Value in a cell.
0
 
LVL 2

Author Comment

by:Howie_Lynn
ID: 40493653
Hi Rob,
Ten  spreadsheets are delivered to me, from seperate sources,  to be combined into one spreadsheet.  I then  correct, combine and sort the completed 5000 record spreadsheet.  It is then delivered to another group for processing.  
One of the spreadsheets has combined alpha numeric data in one column.  Excel cannnot correctly sort this column.   This is what happens when I parse the data in the alpha numerica column:
When I paste the formula into a column, next to the column with the combined alpha numeirc data, this is what happens.  
I use the paste special options value.   The data is seperated and the value then appears in the new column correctly for all records.   The original column is still part of the worksheet.   This has to be removed in order to complete, combine and deliver the combined worksheets.   When I delete the original column, Excel generates a formula error highlite in the new column in red text.  The error indicates the original data is missing.   I "assume" that Excel is using the formula, in the new column, to generate the value displayed in that column.    If this is not typical of Excel's function, perhaps one the office patches from Microsoft has damaged my copy of Excel.  
I then discovered if I insert an additonal  column and copy the generated column into that column using the paste special options value,  that column contains only the values.  I then can delete the other columns without error.

Thanks for your interest,
Howie
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40493913
Paste Special Values DOES NOT USE a formula. It pastes the Values, does exactly what it says it does.
You will never get an error if you correctly use PASTE SPECIAL -> Values  and then delete the source column.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 31

Expert Comment

by:Rob Henson
ID: 40494038
Going by the original question, you have data in one column with combined numeric and alpha values, "45 Barclay Avenue" in your sample.

You then have TWO columns with formulas to split that value into "45" in one column and "Barclay Avenue" in the other.

Correct so far?

In all of the subsequent comments you have stated that you have copied the column, singular. I assume you are copying BOTH columns and pasting values into TWO new columns or into the same two columns, thus overwriting the formulas with values.

Also, can you provide a screenshot of when the situation you describe here happens.

  "Excel generates a formula error highlite in the new column in red text."

This is not a situation I have seen before.

Thanks
Rob H
0
 
LVL 2

Author Comment

by:Howie_Lynn
ID: 40494399
Rob,
This project occurs once a year from the middle of November until the second week of December.  I also print 5000 vouchers for distribution in this time period.  I honestly don't remember this happening before.   However, I have not received a mixed alpha numeric data column in three years.  So, I'm not sure.
The project has been completed successfully and will not repeated until November of 2015.   These spreadsheets are produced on dozens of different computers and combined by the ten coordinators and then sent to me.  All from different sources and probably different versions of OS and Excel.   Anything could have happened to create this problem.   I am just happy I found a solution in order  to  get the distribution completed by the second week of December.  
Is troubleshooting this rare and strange problem worth our time to try and solve?  I'm happy if you are and unless you wish to pursue this,  why don't we just chalk it up  to experience and move on with our lives?
Thanks again for your help,
Howie
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40494623
As is often the case with situations like this, needs must and you get the job done with a manual fix and the situation gets forgotten about until next time when it rears its ugly head.  In your case that won't be until this time next year after a lot of Yuletide sherry and Summertime sangrias has been consumed; in general the whole rigmarole has been forgotten about.

Would it not be better to get a solution now while it is still fresh; get the solution noted in a document relating to the process or in the file itself. Then when the task comes round next year and the same happens again, you have something to refer to to get it sorted. If you're not doing the job next year, you might earn some brownie points for helping out the person is.

Thanks
Rob
0
 
LVL 2

Author Comment

by:Howie_Lynn
ID: 40495149
how do I add a screen shot?
0
 
LVL 2

Author Comment

by:Howie_Lynn
ID: 40495171
I would like to include several screen shots with explanations as to how they where created.  How?
Exchange seems to want a file name,  don't know what your going to do with that.   I want to embed pictures with comments,  not file names.  How can I do that in Exchange?
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40495190
Above the comments box there are various buttons. Furthest right is to embed a picture but you have to save the screen shot and then select the picture file.
0
 
LVL 2

Author Comment

by:Howie_Lynn
ID: 40496489
Hi Rob,

I contacted Microsoft Office Support.   During a remote session, they checked my Excel settings and a small Excel file I had created with mixed alpha numeric data and the parse formula in a top cell of a new column adjacent to the mixed data column.  They said that was a correct procedure.
They attempted to parse the alpha numeric data.  The same problem occurred.  They said that Excel should not do this.  Since I work with a large community of Excel users and spreadsheets they recommended first to upgrade to Office 2013.   If, in fact, it is an Office problem, the upgrade will eliminate that possibility.    We upgraded office but the same problem occurred in the parsing of alpha numeric data in Office 2013.  They set up a call back with level 2 support.   Level 2 support will check the Office Setup in the Registry.  Hopefully they will find and correct a Registry problem.   If not, the only other suggestion would be a complete upgrade to OS 8.
 I don't think so.   I have found a work around and do not find it difficult to do.  The level 1 tech said that when this problem occurs, they would suggest the same workaround to solve the problem that I was using.  Namely copying the new column with both the formula and the parsed date into a third additional column using the past special options value.

Sorry, I don't have better news for you.  I wish I did.  But we have to live with what we have.  The experienced folks at all of our companies are retiring and the new staff is starting to relearn what took the retired staff most of their working lives to learn.  Good luck to all of us.

Thanks for your help, it is appreciated.
Howie
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40540062
I've requested that this question be closed as follows:

Accepted answer: 0 points for Howie_Lynn's comment #a40496489

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
LVL 2

Accepted Solution

by:
Howie_Lynn earned 0 total points
ID: 40533346
Hi MartinLiss,

My last comment to Rob Henson indicated I was waiting for a reply from Micorsoft Pro support, level 3.     My previous two sessions from Microsoft, level one and level two were unable to solve the problem I was experiencing.  After performing remote login and testing, level one and level two indicated that Excel was not working as expected on my system.  They had no explanation why.  With my permission, they upgraded my Office suite to the latest release.  During Level One's retest of the parsing formula on my system, following the upgrade, thier new testing prodced the same result.    They elevated the case to pro support.   Pro support contacted me and and said they required a payment of $500.00 to troubleshoot the failure of the parsing function of Excel on my system.   I wll not pay $500.00.
What would you suggest I do?

I think there should be a section of Expert Exchange for unresoved problems.  Not to have such a section would indicate that this could never be the case.   I have worked with Microsoft and Apple long enough to know they are  not perfect.   And they make mistakes. Some never get fixed.  There should be a section of Experts Exchange to list these problems.   This would be for the benefit of your customers as well as a wake up  call to Microsoft.
What do you think?
Otherwiise, accept my comments as the accepted answer.  And properly  close this case so that it is available to  your other customers for reference.
0
 
LVL 2

Author Comment

by:Howie_Lynn
ID: 40540064
Please read my last comment.   In addition,  I would perfer that several of the experts that responded to my comment create a small spreadheet on thier systems.   First, upadating thier system with the latest patches and installing the latest version of MS Office.  Then they should attempt to replicate the problem on thier systems.  Otherwise I will not know if my system is causing the error or if it is Microsoft's error. Regardless,  the only thing that has changed in my Office Suite since last year, is the monthly patches installed by Miicrosoft.  Microsoft's fee of $500.00 seems unreasonable.

Howie
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40540079
To duplicate YOU would need to provide CLEAR step by step AND a sample of your data in a sheet AND the desired result.
From what you explain, excel works perfectly every time I try.
Perhaps some samples AND clear steps of what you tried will help.
0
 
LVL 2

Author Comment

by:Howie_Lynn
ID: 40541867
Neilsr,
Did you try both the =Replace and the =Left formulas?  Did you then examine the new cell created to see if it  contained only the desired data transfered?  Did you then erase the original mixed data column with out the resulting error in the new cell?

Also Microsoft level one and level two support, upon a remote logon to  my  system and remote testing  the two formulas on a small spreadsheet I created,  could not resolve the problem.  They had  the same  error after they installed the latest version of Miicrosoft Office on my system.  Level two sent the open case to level three,

Level one and   level two thought it might be  a registry problem.
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40541870
Like we said, unless you provide a sample of your data with clear cut objectives.....
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40555174
Clearly you are not prepared to provide the information requested so that we can help you in this.  On more than one occasion you have been asked for examples, samples and more info but have just ignored the requests with no response.

We can only help the people who actually want to be helped and who partake in the solution.
0
 
LVL 2

Author Closing Comment

by:Howie_Lynn
ID: 40563900
The problem could not be solved by Microsoft Excel Technical support unless I payed them $500.00.   Level one and level two Microsoft Tech Support, after remote examining the problem on my computer with a remote login,  said it might be a registry problem.   Level two escalated the case to level three.  I did not want to pay $500.00 for a "possible" solution.
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40563920
WE are not Microsoft and did not ask for $500. but we did ask for information that you would not provide, no wonder it never got resolved
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

747 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

13 Experts available now in Live!

Get 1:1 Help Now