Excel data clean up

Question

I am needing to clean up some data in Excel.  I have a source that provides unnecessary information around the data that I actually need (which varies in length as it contains names) and I would like an easy way to remove that unnecessary information without having to edit each cell.  There is a LOT of entries (over 2,500) so any help would be appreciated.  As an example, I have source data that provides an activity "code_definition_NAME_number_confirmation".  The information at the beginning is all of the same length (if that is important) and I am looking to just use the NAME data that is present (which is understandably of differing lengths, again, if that is important).  How do I extract just the name or eliminate the unneeded information?  The NAME data is in all caps if that makes a difference.

"T-123 This information Is To be Removed FIRST MI LASTNAME 12345678-9  N" is a generic example of what I am working with.  Other examples would be "T-123 This information Is To be Removed FIRSTFULL M LASTFULLNAME 012345678-9  N" just to show how the relevant data is of varying size.

I have been using the MID formula under the Text functions because I do have duplicate names so I can just drag the formula but I have several hundred individual names of varying lengths so this is still quite time consuming.  I'm hoping that since what I need to extract is in ALL CAPS in the middle of the data that there might be a macro I can install to pull just that info so I can drag it to all entries.

Answer 1

= Table.SplitColumn(#"Removed Columns", "Remarks.2", Splitter.SplitTextByPositions({38}), {"NAME"})

You can manually tweak the generated PowerQuery command to immediately give the new column the correct name, rather than using a second step.

Answer 2

Hi,

I have solved this question via 2 methods - formulas [column E] and Power Query (Data > Get & Transform in Excel 2016 and higher versions) [column C].  You may download the workbook from here.  Add data to the blue range and click on Data > Refresh All for result in column C to update.

Hope this helps.

Answer 3

If as you say all of the leading garbage is the same length, you can use the text-to-column feature.

.

If there is something different, please provide us with an example file demonstrating the various data layouts.

Common spreadsheet data issues and how to solve them          2020 03 18

https://www.accountingweb.co.uk/tech/excel/common-spreadsheet-data-issues-and-how-to-solve-them

Sometimes when you import data from text files or external databases, numbers (including dates) get stored as text. Also, some users are in the habit of typing an apostrophe (‘) before a number to make it text. This could create serious issues if you are using these cells in calculations. Here’s two ways to convert numbers (that are not dates) stored as text back into numbers:

.  *  Paste Special -

.  *  Text to Columns

.  *  Dealing with duplicates

.  *  Removing additional spaces and non-printable characters

.  *  Parsing Data

.  *  Considering blank cells

.  *  Changing text case

.  *  Removing formatting

.

VLOOKUP Not Working? Convert Text to Date? (Excel Text to Columns)    

https://www.xelplus.com/excel-text-to-columns/

When working with imported data in Excel, it is not uncommon to encounter issues with numbers and dates not being recognized properly. Importing data via Power Query provides us with a plethora of tools to manipulate and interpret our data.  Importing data from Excel add-ins that pull content from external sources such as Oracle or SAP will often not perform content analysis.  The add-ins simply pull content and dump it into Excel.  Manually copying and pasting data from a list or database can also prove problematic when it comes to searches and comparisons.

.

Upload Example - Trouble Shooting - Share Personal OneDrive File (not Business OneDrive)

.

Trouble shooting problems in files can be like a visit to the dentist, a long, slow painful process of us trying to “extract” the clues needed to recognize the problem in a back and forth flow of questions and answers so that we can come up with a / “the” solution.

.

Often it is faster and easier for everyone if we have a “sample file” get "hands on", to look at, and to “play with”.

.

This next link provides some tips on setting up a sample file and specific instructions for uploading and "sharing" it for us to access:

.

https://answers.microsoft.com/en-us/windows/forum/windows_other-winapps/trouble-shooting-share-onedrive-file/a231a097-bcbf-4e34-ad6c-a33118baf471

**************************************

.

That article links to macros to randomize text in Word and numbers in Excel

.

This article describes another way of sharing Office files:

2020 03 09- Share a cloud stored document from Microsoft Office

https://office-watch.com/2020/share-cloud-stored-document-microsoft-office/

Using “Invites” to specific people from inside Office apps. Modify document access permissions and add a “note” with “how to” instructions for recipient

.

**************************************

.

The first 2 minutes of this video gives an example of What I mean by a “simple” example. Use short simple names and quantities, and just 2 or 3 rows per sample data you want calculations done on.  You want simple numbers so you can do the math checks in your head.

Col- **Stacking Columns of Data (Unpivot, Split Columns by delimiter, and everything!)****(PowerQuery)******2017 04 16

http://ozdusoleil.com/2017/04/16/power-query-stacking-columns-of-data-unpivot-split-columns-by-delimiter-and-everything/

We’ve got headers and a large number of paired columns that need to be condensed down to just 2 columns. 

.

**************************************

.