Solved

Why is a template to separate numbers from text in a excel spreadsheet no longer working?

Posted on 2014-11-18
4
248 Views
Last Modified: 2014-11-19
A template to separate data in mixed format in an Excel column  no longer functions.

23 Echo Lane

Could be separated by:
=LEFT(A2,FIND(" ",A2)-1)   pasted into a new adjacent blank column
and
=REPLACE(A2,1,FIND(" ",A2,"")
pasted into a new adjacent blank column on the opposite side of the target column

resulted in:

23 in one column and Echo Lane in another column with the original column in between the two new columns

Now an error "#NAME?"  is returned instead of the parsing of the data.
0
Comment
Question by:Howie Kay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 167 total points
ID: 40451081
If this workbook has, or used to have, macros in it, you might have a broken reference.

1) Hit Alt+F11 to get to the VB Editor
2) In the menu, go to Tools / References

Now see if any are listed as missing.
0
 
LVL 8

Accepted Solution

by:
Eric Flamm earned 333 total points
ID: 40451105
You're missing a close parentheses on the replace formula - not sure why that would return #Name, however.
0
 
LVL 8

Assisted Solution

by:Eric Flamm
Eric Flamm earned 333 total points
ID: 40451111
=REPLACE(A9,1,FIND(" ",A9),"")

Open in new window

is the correct syntax
0
 
LVL 2

Author Comment

by:Howie Kay
ID: 40451317
Its a standalone page,  just a typed in listing of names and addresses.   It was an .xls and I saved it as a macro enabled spreadsheet.  I'll try and save it as the latest extension xlsx.  I saved as separate files. The templates failed in each one. Do you think there is something in the spreadsheet causing this?  I receive 10 spreadsheets from separate sources.   This is the first one with mixed address columns.  I'll create a test spreadsheet and see if the templates work.
Thanks,
Howie
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Gain an elementary understanding of Blockchain technology.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

691 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