<div>
Hi, 
 
because you test Len(sValue) &lt;&nbsp;5 
 
and DD-03-A has a len of 7 
 
Regards
</div>

<div>
I changed the first Len value to 7 and still doesn't work. 
Is the &nbsp;&quot;ElseIf nColScan = 10 Then&quot; &nbsp; wrong or something? 
Thanks
</div>

<div>
then try 

<pre><code class="code-1 nolinks" id="code-20-41406653-1">ElseIf nColScan = 10 Then
 '151208 s4p remove hypens
 sValue = Trim(Replace(sValue, &quot;-&quot;, &quot;&quot;)) 'remove hypens, trim leading and trailing spaces
 If Len(sValue) &lt;= 5 Then
 nRowNext = nRowScan + nRowDataSkip 
</code></pre>
</div>

<div>
Hmmm, 
I am Debugging - Compiling, then saving the VBA then saving the Excel wb after pasting the code and still doesnt remove hyphens. 
Any Idea? 
 
Thanks
</div>

<div>
is there a difference in the 2 &nbsp;msgboxes 
 

<pre><code class="code-1 nolinks" id="code-20-41406673-1">ElseIf nColScan = 10 Then
 '151208 s4p remove hypens
 MsgBox sValue
 sValue = Trim(Replace(sValue, &quot;-&quot;, &quot;&quot;)) 'remove hypens, trim leading and trailing spaces
 MsgBox sValue
 If Len(sValue) &lt;= 5 Then
 nRowNext = nRowScan + nRowDataSkip
</code></pre>
</div>

<div>
HI there, 
Here is all the code for that part 

<pre><code class="code-10 nolinks" id="code-20-41406690-1"> ElseIf nColScan = 10 Then
 '151208 s4p remove hypens
 sValue = Trim(Replace(sValue, &quot;-&quot;, &quot;&quot;)) 'remove hypens, trim leading and trailing spaces
 If Len(sValue) &lt;= 5 Then
 nRowNext = nRowScan + nRowDataSkip
 
 With wsData
 'color location just scanned pale yellow and lock
 With .Range(&quot;J&quot; &amp; nRowScan)
 'if last character is B or C, change to A
' If UCase(Right(.Value, 1)) = &quot;B&quot; Or UCase(Right(.Value, 1)) = &quot;C&quot; Then

 'if last character is &lt;&gt; &quot;A&quot;, change to A
 If UCase(Right(.Value, 1)) &lt;&gt; &quot;A&quot; Then
 .Value = Left(.Value, Len(.Value) - 1) &amp; &quot;A&quot; '151208 s4p
 End If
 .Interior.Color = RGB(255, 240, 180) 'location is yellow 250,230,200
 .Locked = True
 End With
 'fill in &quot;skid&quot; for the next row
 .Range(&quot;A&quot; &amp; nRowNext) = &quot;Skid&quot;
 'increment skid number
 .Range(&quot;B&quot; &amp; nRowNext) = wsData.Range(&quot;B&quot; &amp; nRowScan) + 1
 'select next Item Number cell and change its color
 With .Range(&quot;D&quot; &amp; nRowNext)
Debug.Print nRowNext
 .Interior.Color = RGB(255, 255, 0) 'bright yellow for active cell
 .Locked = False
 .Select
 End With
 End With
 Else
 With Target
 .Locked = False
 .Select
 End With
 MsgBox &quot;Please scan a LOCATION&quot;, , &quot;Error: Scan LOCATION&quot;
 GoTo Proc_Exit
 End If
 Else
' MsgBox &quot;Scan in wrong column (&quot; &amp; nColScan &amp; &quot;), contact Chris&quot;, , &quot;Error: Scan in wrong column&quot;
 wsData.Cells(nRowScan, 4).Select
 GoTo Proc_Exit
 End If
 
Proc_Exit:
 On Error Resume Next
 Application.EnableEvents = True
 'put protection back on
 Call ABC

 Set wsData = Nothing
 Exit Sub
</code></pre>
</div>

<div>
I am afraid it is a bit out of my reach of understanding. 
Thanks for having a look. 
 
I tried just doing a find/replace in the range of the column, but when I ran the macro it messed up the rest of the sheet. I think because the sheet is still running something in the background. 
Chris
</div>

<div>
Do you have a workbook, or sample, that you can attach?
</div>

<div>
Hi MArtin. 
I have to strip the data. 
Hang on 
Thanks
</div>

<div>
OK Here we go. 
This is the long code that you can copy into D4 for the first code 
11032640 20OCT2015 J5572 5 
 
Then the cursor moves to J4 waiting for the next short code, us one with a hyphen 
DD-10-B 
DD13B 
DD-02-A 
DD04A 
 
Just copy the codes and select D4-Paste and J4 Paste, the rest is automatic. 
It changes to B to A, which is great, works fine. 
 
Just doesn't remove the Hyphen. 
Thanks 
<a href="https://filedb.experts-exchange.com/incoming/2016/01_w03/1068647/Location-Allocation-test-remove-hyp.xlsm" target="_blank" class="file-inline" title="Remove Hyphen----- (53 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Location-Allocation-test-remove-hyp.xlsm</a>
</div>

Location-Allocation-test-remove-hyp.xlsm

<div>
@Martin 
Yup Just needed Line 5. Boy.... My fingers were crossed. 
I am nervous messing around with code. 
But I think I got it. 
Still testing. 
Chris
</div>

<div>
Good Job, Thanks for the help. 
Appreciate it. 
Worked well.
</div>

<div>
You're welcome and I'm glad I was able to help. 
 
In <a href="https://www.experts-exchange.com/M_258447.html" rel="ugc">my profile</a> you'll find links to some articles I've written that may interest you. 
 
Marty - MVP 2009 to 2015 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Experts Exchange MVE 2015 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Experts-Exchange Top Expert Visual Basic Classic 2012 to 2015
</div>

<div>
<div class="content wysiwyg-content">
I had another great expert help me with this one, but I know they are too busy and I don't want to bug them on this one. 
 
Why isn't this code working? 
 
I have two kinds of codes being entered by a user: 
DD-03-A &nbsp; &nbsp;......AND...... &nbsp; &nbsp; &nbsp;DD03A &nbsp; 
 
Why isn't this VBA working? 
 

<pre><code class="code-1 nolinks" id="code-10-28913023-1">ElseIf nColScan = 10 Then
 '151208 s4p remove hypens
 If Len(sValue) &lt; 5 Then
 sValue = Trim(Replace(sValue, &quot;-&quot;, &quot;&quot;)) 'remove hypens, trim leading and trailing spaces
 End If
 If Len(sValue) &lt;= 7 Then
 nRowNext = nRowScan + nRowDataSkip 
</code></pre>
 
Thanks Experts 
Chris
</div>
</div>

I had another great expert help me with this one, but I know they are too busy and I don't want to bug them on this one.

Why isn't this code working?

I have two kinds of codes being entered by a user:
DD-03-A    ......AND......      DD03A   

Why isn't this VBA working?

(CODE)

Thanks Experts
Chris

Removing Hyphen from number string using VBA

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

VBScript (Visual Basic Scripting Edition) is an interpreted scripting language developed by Microsoft that is modeled on Visual Basic, but with some important differences. VBScript is commonly used for automating administrative and other tasks in Windows operating systems (by means of the Windows Script Host) and for server-side scripting in ASP web applications. It is also used for client-side scripting in Internet Explorer, specifically in intranet web applications.

VB Script

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.