Solved

Excel - script required to delete names

Posted on 2016-09-04
4
31 Views
Last Modified: 2016-09-04
I  need to regularly update an address file, and part of the job is to remove building names within the address.
I'd like to be able to add the names to a script, that when it is run, it will delete the names from the list.
The attached file shows an example of the original list, and the required list.
EXAMPLE-FILE.xlsx
0
Comment
Question by:gregfthompson
  • 2
  • 2
4 Comments
 

Author Comment

by:gregfthompson
Comment Utility
the script needs capacity for perhaps several hundred names.
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
Comment Utility
Please try this.....
Function DeleteNames(str As String) As String
Dim i As Long
For i = 1 To Len(str)
   If IsNumeric(Mid(str, i, 1)) And Mid(str, i + 1, 1) = " " Then
      DeleteNames = Left(str, i + 1)
      Exit For
   End If
Next i
End Function

Open in new window

And use it like this....
In C2
=DeleteNames(A2)

Open in new window

and copy down.

For details, refer to the attached.
EXAMPLE-FILE.xlsm
0
 

Author Closing Comment

by:gregfthompson
Comment Utility
Excellent work. Thank you.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
Comment Utility
You're welcome. Glad to help.
Thanks for the feedback.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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 viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

772 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

16 Experts available now in Live!

Get 1:1 Help Now