Combing 2 Columns That Contain Over 16 Numbers And Have Them Display Correctly

D

dmcgetti

Board Regular
Joined
Feb 16, 2015
Messages
66
I have 2 columns that I am combing via VBA, the first column contains invoice numbers that are 7 digits long and the second column contains part numbers that are 12 digits long and contain a "-" and a "*". Everything works fine until I remove the "-" and the "*", I get a bunch of "#"'s and if I convert the column to text I get a scientific notation. I know that if I was manually typing in the cell I could add " ' ", but how do I do that, or similar, via VBA.

Code: 
    Range("Table1[Lookup]").NumberFormat = "Text"    
    Range("Table1[Lookup]").FormulaLocal = "=[@InvoiceNumber]&[@PartNumber]"
    Range("Table1[Lookup]").Copy
    Range("Table1[Lookup]").PasteSpecial Paste:=xlPasteValues
    Range("Table1[Lookup]").NumberFormat = "Text"
    Range("Table1[Lookup]").Select
    Selection.Replace What:="~*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("Table1[Lookup]").Select
    Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
[TABLE="width: 427"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]inv[/TD]
[TD]part[/TD]
[TD]combine[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1234567[/TD]
[TD]987-65432*11[/TD]
[TD]1234567987-65432*11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]is this what you want ?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
[TABLE="width: 406"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]inv[/TD]
[TD]part[/TD]
[TD]combine[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1234567[/TD]
[TD]987-65432*11[/TD]
[TD]1234567987-65432*11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]is this what you want ?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]or this[/TD]
[TD]12345679876543211[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
oldbrewer said:
[TABLE="width: 406"]
<tbody>[TR]
[TD]inv
[/TD]
[TD]part
[/TD]
[TD]combine
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1234567
[/TD]
[TD]987-65432*11
[/TD]
[TD]1234567987-65432*11
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]is this what you want ?
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]or this
[/TD]
[TD]12345679876543211
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

This
 
Upvote 0
dmcgetti said:
I have 2 columns that I am combing via VBA, the first column contains invoice numbers that are 7 digits long and the second column contains part numbers that are 12 digits long and contain a "-" and a "*". Everything works fine until I remove the "-" and the "*", I get a bunch of "#"'s and if I convert the column to text I get a scientific notation. I know that if I was manually typing in the cell I could add " ' ", but how do I do that, or similar, via VBA.

Code: 
    Range("Table1[Lookup]").NumberFormat = "Text"    
    [B][COLOR="#FF0000"]Range("Table1[Lookup]").FormulaLocal = "=[@InvoiceNumber]&[@PartNumber]"[/COLOR][/B]
    Range("Table1[Lookup]").Copy
    Range("Table1[Lookup]").PasteSpecial Paste:=xlPasteValues
    Range("Table1[Lookup]").NumberFormat = "Text"
    Range("Table1[Lookup]").Select
    Selection.Replace What:="~*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("Table1[Lookup]").Select
    Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
Click to expand...
Does changing the highlighted line of code to this make it work for you...

Range("Table1[Lookup]").FormulaLocal = "=""'""&[@InvoiceNumber]&[@PartNumber]"
 
Upvote 0
dmcgetti said:
No luck, thanks
Click to expand...
Describe "no luck" for me. What actually happened or didn't happen that should have happened? Just so you know, the code worked for me when I set up an Excel table object, named it "Table1" and put columns with the headers "InvoiceNumber", "PartNumber" and "Lookup" in it and then put the values 'oldbrewer' showed in the columns he labeled "inv" and "part" (Message #3 ) into them.
 
Last edited:
Upvote 0
Rick Rothstein said:
Describe "no luck" for me. What actually happened or didn't happen that should have happened? Just so you know, the code worked for me when I set up an Excel table object, named it "Table1" and put columns with the headers "InvoiceNumber", "PartNumber" and "Lookup" in it and then put the values 'oldbrewer' showed in the columns he labeled "inv" and "part" (Message #3 ) into them.
Click to expand...

Scratch that, it did "work", I had a typo.

That combines them, but the " ' " is in the cells that are all numbers, and I need the cells to just contain the InvoiceNumber and PartNumber because that column is used as look up field.
 
Last edited:
Upvote 0
dmcgetti said:
Scratch that, it did "work", I had a typo.

That combines them, but the " ' " is in the cells that are all numbers, and I need the cells to just contain the InvoiceNumber and PartNumber because that column is used as look up field.
Click to expand...
The apostrophe should only be appearing in the Formula Bar... it should not be visible in the cell itself. As long as this is the case, your lookup should work as long as what you are looking up is text (otherwise all of the digits will not be correct in the value you are looking up). If you are still having trouble, show us the code that is doing the look up.
 
Upvote 0
Rick Rothstein said:
The apostrophe should only be appearing in the Formula Bar... it should not be visible in the cell itself. As long as this is the case, your lookup should work as long as what you are looking up is text (otherwise all of the digits will not be correct in the value you are looking up). If you are still having trouble, show us the code that is doing the look up.
Click to expand...

It is showing up in the cell, but it is working none the less, so thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Assistance with excel macro
Replies
3
Views
244
dreid1011
dreid1011
L
  • Question Question
How to loop to find and replace with the specific cells
Replies
2
Views
297
Linda Lo
L
N
  • Question Question
Not understanding loop in vba
Replies
7
Views
296
jolivanes
jolivanes
D
  • Solved
Range selection specific column with End(xlDown)
Replies
8
Views
296
dwalc
D
BAKELOVEMORE
  • Solved
Capturing result in cell from formula and Selection.Replace
Replies
5
Views
295
Michael M
Michael M

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top