How to replace parse formulas with absolute Values

Copy the column and the use paste special and select Values. That will paste only the results.

Menu:  Home, Clipboard group, Paste, Paste Special...

Here's what the Paste Special dialog looks like:

-Glenn

Yes like I already said, Use PASTE SPECIAL, VALUES!

Or use the Text to Columns function to split the data in place rather than using formulas.

Thanks
Rob H

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.

Also, I would like to name these functions so they can be easily accessed.

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.

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

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.

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.

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

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.

Can you clarify what you mean when you say

"value" is displayed on top of the formula

Thanks
Rob H

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.

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

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.

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

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

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

how do I add a screen shot?

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?

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.

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

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.

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

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.

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.

Like we said, unless you provide a sample of your data with clear cut objectives.....

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.

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.

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