DataPedia
  • DataPedia - Home
  • Who we are
  • Business Management System
    • DataPedia - Business Management System
    • BAS
    • Mobile Business
    • Book a demo
  • Excel Consulting
    • Ask Us Any Excel Question
    • Excel Tips and Tricks for beginners
  • Contact Us

Stripping Out Unwanted Characters - Excel Basics

12/12/2015

0 Comments

 

The raw file has characters that need to be removed...

Picture
Sometimes when you run an extract, the file contains data characters that you don't want in your file list.

These could be commas, dashes, stars or any weird character that is added with source files.   Want to strip this out quickly?  Here is a formula that can help.

Note - there is an easier way to do this using "text to columns" - but lets keep it interesting and use just formula.

Strip out the character using formula

Picture
We can develop a formula that finds any unwanted character and replace it with a better character. 

So in this case, we need to find the "-" and replace it with a " " instead. 
In cell B1 enter this formula:  

​=REPLACE(A1, FIND("-",A1), 1, " ")

What this is saying:

= Replace formula allows you to search within a cell to find a special character

A1 is telling the formula where the cell is that has the problem

FIND("-",A1) is telling the formula WHERE the incorrect character is


1 is saying to replace just 1 character (i.e. change the - to a space)

And finally the " " is telling the formula when you find the "-" replace it with the " "
You can have fun with this formula by saying change the "-" to a "^" by using this formula:

​=REPLACE(A1, FIND("-",A1), 1, "^")
Once you are happy with it - fill the formula down you are done.
0 Comments



Leave a Reply.

    Author

    Abe Dahbache has been working with EXCEL for over 15 years. He is a Microsoft Office User Specialist and has been developing solutions in Excel for some of the worlds largest companies.

    Archives

    December 2015
    November 2013

    Categories

    All
    Basic Vlookup
    Cleanse
    Concatenate
    Data Cleanse
    Data Strip
    Excel
    Lookup
    Reference
    Values
    Vlookup

    RSS Feed

Services

Business Management System
Excel Consulting
Custom Mobile Solutions

Company

About
Our App
Excel Tips

Support

Contact
​Log a support ticket
Help using DataPedia app
Terms of Use
© COPYRIGHT 2015. ALL RIGHTS RESERVED.
  • DataPedia - Home
  • Who we are
  • Business Management System
    • DataPedia - Business Management System
    • BAS
    • Mobile Business
    • Book a demo
  • Excel Consulting
    • Ask Us Any Excel Question
    • Excel Tips and Tricks for beginners
  • Contact Us