<div>
Excel 2016<br><br>I have data like the example above that has 10 numbers that I want to remove from the data. Some of the data in the list does not contain the numbers so it is OK like it is <br><br>
</div>


<div>
I believe Power Query has the option to split numbers from letters. &nbsp;In Power Query :&nbsp;<table><tbody><tr><td>Transform&gt;Split &nbsp;Column&gt; By Digit to Non Digit</td></tr></tbody></table><br><br>
</div>


<div>
@Tom<br>That's true but it will not replace the original data in the first place rather it would insert a new table with the transformed data. If intention is to replace the data in place, VBA is the only way to achieve this.<br>Also, from the description it seems only removing the first 10 digits from the string is required if I got it right.<br>i.e. from the original string <strong>0013976475 00001 Chip Mill Maintenance</strong>, the expected output is <strong>00001 Chip Mill Maintenance</strong>.<br>Is my assumption correct Kim?
</div>


<div>
This assumes that the data is in column 'A' starting in row 1 and that you want the results in columns B to D. Columns B and C should be formatted as Text.<br />

<pre><code class="code-10 nolinks" id="code-20-43284445-1"><span>Sub SplitData()</span>
<span>Dim lngLastRow As Long</span>
<span>Dim lngRow As Long</span>
<span>Dim strParts() As String</span>
<span>Dim lngPart As Long</span>
<span>Dim ws As Worksheet</span>
<span></span>
<span>Set ws = ActiveSheet</span>
<span>With ws</span>
<span>    lngLastRow = .Range(&quot;A1048576&quot;).End(xlUp).Row</span>
<span>    For lngRow = 1 To lngLastRow</span>
<span>        strParts = Split(.Cells(lngRow, &quot;A&quot;), &quot; &quot;)</span>
<span>        .Cells(lngRow, &quot;B&quot;) = strParts(0)</span>
<span>        .Cells(lngRow, &quot;C&quot;) = strParts(1)</span>
<span>        For lngPart = 2 To UBound(strParts) - 1</span>
<span>            .Cells(lngRow, &quot;D&quot;) = .Cells(lngRow, &quot;D&quot;) &amp; strParts(lngPart) &amp; &quot; &quot;</span>
<span>        Next</span>
<span>        .Cells(lngRow, &quot;D&quot;) = .Cells(lngRow, &quot;D&quot;) &amp; strParts(UBound(strParts))</span>
<span>    Next</span>
<span>End With</span>
<span>End Sub</span>
</code></pre>
</div>


<div>
The &nbsp;00001 would also be removed only keeping the description. I can use text to columns to split and then only keep the description. I was just wondering if there was a better way to accomplish this.<br>&nbsp;
</div>


<div>
This assumes that the data is in column 'A' starting in row 1. It ignores the numbers and puts the description in column 'B'.<br />

<pre><code class="code-10 nolinks" id="code-20-43284449-1"><span>Sub SplitData()</span>
<span>Dim lngLastRow As Long</span>
<span>Dim lngRow As Long</span>
<span>Dim strParts() As String</span>
<span>Dim lngPart As Long</span>
<span>Dim ws As Worksheet</span>
<span></span>
<span>Set ws = ActiveSheet</span>
<span>With ws</span>
<span>    lngLastRow = .Range(&quot;A1048576&quot;).End(xlUp).Row</span>
<span>    For lngRow = 1 To lngLastRow</span>
<span>        strParts = Split(.Cells(lngRow, &quot;A&quot;), &quot; &quot;)</span>
<span>        For lngPart = 2 To UBound(strParts) - 1</span>
<span>            .Cells(lngRow, &quot;B&quot;) = .Cells(lngRow, &quot;B&quot;) &amp; strParts(lngPart) &amp; &quot; &quot;</span>
<span>        Next</span>
<span>        .Cells(lngRow, &quot;B&quot;) = .Cells(lngRow, &quot;B&quot;) &amp; strParts(UBound(strParts))</span>
<span>    Next</span>
<span>End With</span>
<span>End Sub</span>
</code></pre>
</div>


<div>
If you only want to replace the numbers from the string, you may try the following code.<br>In the attached click the button called "Get Description" to run the code.<br><br><pre><code class="code-10 nolinks" id="code-20-43284462-1"><span>Sub GetDescription()</span>
<span>Dim ws &nbsp; &nbsp; &nbsp;As Worksheet</span>
<span>Dim lr &nbsp; &nbsp; &nbsp;As Long</span>
<span>Dim x &nbsp; &nbsp; &nbsp; As Variant</span>
<span>Dim y &nbsp; &nbsp; &nbsp; As Variant</span>
<span>Dim i &nbsp; &nbsp; &nbsp; As Long</span>
<span>Dim Matches As Object</span>
<span></span>
<span>Set ws = ThisWorkbook.Worksheets("Sheet1")</span>
<span>lr = ws.Cells(Rows.Count, 1).End(xlUp).Row</span>
<span>x = ws.Range("A1").CurrentRegion.Value</span>
<span>ReDim y(1 To lr, 1 To 1)</span>
<span>With CreateObject("VBScript.RegExp")</span>
<span>&nbsp; &nbsp; .Global = False</span>
<span>&nbsp; &nbsp; .Pattern = "^\d+ \d+"</span>
<span>&nbsp; &nbsp; For i = 1 To UBound(x, 1)</span>
<span>&nbsp; &nbsp; &nbsp; &nbsp; If .Test(x(i, 1)) Then</span>
<span>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Set Matches = .Execute(x(i, 1))</span>
<span>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; y(i, 1) = VBA.Trim(Replace(x(i, 1), Matches(0), ""))</span>
<span>&nbsp; &nbsp; &nbsp; &nbsp; Else</span>
<span>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; y(i, 1) = x(i, 1)</span>
<span>&nbsp; &nbsp; &nbsp; &nbsp; End If</span>
<span>&nbsp; &nbsp; Next i</span>
<span>End With</span>
<span>ws.Range("A1").Resize(lr, 1).Value = y</span>
<span>End Sub</span>
</code></pre><br><br><a href="https://filedb.experts-exchange.com/incoming/2021/05_w19/1559455/Get-Description.xlsm" rel="ugc">Get Description.xlsm</a>
</div>


Get-Description.xlsm

Financial Analyst

Kim Hart

<div>
Also if your strings always follow the following pattern...<br>&lt;10 digits number&gt;&lt;space&gt;&lt;5 digits number&gt;&lt;space&gt;&lt;desired description&gt;<br>and assuming your strings start from A2, you may try the following formula in B2<br><pre><code class="code-1 nolinks" id="code-20-43284470-1"><span>=IF(ISNUMBER(LEFT(A2)+0),MID(A2,18,255),A2)</span>
</code></pre>and then copy it down. Then copy all the formula cells and paste them back to the original column (column A in this case) as VALUES only and delete the formula cells in the end.<br><br><br>
</div>


<div>
If the only numbers in the string are at the beginning, ie not in the description part, you can use this code to remove all numbers from the selected range.<br><br><pre><code class="code-10 nolinks" id="code-20-43284846-1"><span>Sub RemoveNumbers()</span>
<span>For I = 0 To 9</span>
<span>&nbsp; &nbsp; Selection.Replace What:=I, Replacement:="", LookAt:=xlPart, _</span>
<span>&nbsp; &nbsp; &nbsp; &nbsp; SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _</span>
<span>&nbsp; &nbsp; &nbsp; &nbsp; ReplaceFormat:=False</span>
<span>Next I</span>
<span>For Each Cell In Selection</span>
<span>&nbsp; &nbsp; Cell.Value = Trim(Cell.Value)</span>
<span>Next Cell</span>
<span>End Sub</span>
<span></span>
</code></pre><br>
</div>


<div>
Some of my data has the string that I included above and some of it does not. I did figure out how to separate the numbers from the text by using text to columns but that did not solve my issue because the data that had the numbers does not contain enough text to match it with other data after the numbers have been removed. In the end I want the data to match in a pivot table so that it no longer shows in the view if there is a debit and credit. All of the responses above have been very helpful but I think now I have to figure out the matching issue. I have included a screenshot of an example. Thanks!<br><table><tbody><tr><td>GE to perform the Annual Inspection TG 5</td></tr></tbody></table><br><table><tbody><tr><td>0014956695 00001 GE to perform the Annua</td></tr></tbody></table><br>
</div>


<div>
So, in summary, the numbers are not of value to you. &nbsp;You just want to remove them so you can match the text showing on the debits and credits. &nbsp;And the problem is sometimes the text gets truncated when the number is removed. &nbsp;Is this correct?
</div>


<div>
As always, a sample file would be helpful.
</div>


S--ACCOUNTING-CheckBook-Month-End--.xlsx

<div>
So, I do not see a clear way of matching the debit/credit "Pivot Description" so the debit eliminates the credit. &nbsp;You could parse out the Pivot Description so only the first 20 characters show, and in the case of your example, they would match up. &nbsp;But I suspect this would create other issues with other transactions. &nbsp;Also I think there is one example (see my spreadsheet, sheet1, rows 17-18) where this would not work as the Pivot Descriptions are totally different. &nbsp;The only way something like this would work would be if there were strict requirements on how transaction fields were coded. &nbsp;Even then there would be instances where things that should match up, won't. &nbsp;<br><br>The process of matching up may be a manual process that could be handled by adding a Absolute Value Column and sorting on this to see matching values. &nbsp;I did this in sheet1, columns Absolute Value and Match. &nbsp;This is an ugly way of matching, but it can work better than scanning all transactions. &nbsp;<br><br>Another way to approach it would be using the system assigned columns to bring transactions together. &nbsp;For instance, cost center, cost element, Area, Unique, etc. &nbsp;This may not totally match transactions in a satisfactory way to eliminate values in the pivot table, but working with system assigned fields provides a more reliable way of ensuring someone has not used descriptions that do not match up . &nbsp;<br><br>My recommendation is you continue to look at options like those I suggest to see the best way to get the reconciliation to match up. &nbsp;I know this does not give you what you asked for, but even by parsing the code (as suggested for matching up the example you provided) will most likely end up creating more problems than solving.<br><br>If any other experts have suggestions, please chime in.....<br><br><a href="https://filedb.experts-exchange.com/incoming/2021/05_w20/1561097/S--ACCOUNTING-CheckBook-Month-End--.xlsx" rel="ugc">S--ACCOUNTING-CheckBook-Month-End--2.xlsx</a><br><br>
</div>


<div>
<div class="content wysiwyg-content fr-view">
I can split this up using text to columns but it put the information in 5 different cells. Is there a better way to just split the numbers from the letters? I have a lot of data to manipulate and only want the letters for description <br><table><tbody><tr><td>0013976475 00001 Chip Mill Maintenance</td></tr></tbody></table>
</div>
</div>



I can split this up using text to columns but it put the information in 5 different cells. Is there a better way to just split the numbers from the letters? I have a lot of data to manipulate and only want the letters for description
0013976475 00001 Chip Mill Maintenance

What is the best way to split information in a cell that contains numbers and words while keeping the words together?

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.