Mobile phones are truncated and 0 is missing VBA

vbanewbie68

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Dear Sir or Madam

1637149764856.png


For using a macro I download a data report from the website's platform, and one of the donor’s mobile phones has an error, the numbers are truncated. Please see my screenshot. How do I resolve this please?

Also, the ‘0’ digit are missing from the start of the mobile numbers as well. What is the best way of doing this?

For the above, I am using a macro file to download a report.

Hope this makes sense.

Best Regards

vbanewbie68
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,159
Office Version
  1. 365
Platform
  1. Windows
For the above, I am using a macro file to download a report.
It looks like the phone numbers are being brought in as numbers, which will drop leading zeroes and bring in long numbers in scientific notation.
We do not want to do that. We want to bring in the values as text, so those things are not lost/changed.

Can you post the VBA code behind that macro?
 

vbanewbie68

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
It looks like the phone numbers are being brought in as numbers, which will drop leading zeroes and bring in long numbers in scientific notation.
We do not want to do that. We want to bring in the values as text, so those things are not lost/changed.

Can you post the VBA code behind that macro?
1637151001745.png


Imprt.Cells(x, OptInSMSNumber).Value =
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,159
Office Version
  1. 365
Platform
  1. Windows
I think we need to see the entire code, not just one line.
 

vbanewbie68

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Sub Add_Columns()

imprt.Cells(1, 1).End(xlToRight).Select ' To illustrate what this range refers to
LastColumn = imprt.Cells(1, 1).End(xlToRight).Column

Range(Cells(1, LastColumn + 1), Cells(1, LastColumn + 28)).Select ' to illustrate what this range refers to
Range(Cells(1, LastColumn + 1), Cells(1, LastColumn + 28)).Value = Array("Cons Code", "Gift Type", "Gift Subtype", "Gift Date", "Gift Campaign", "Gift Fund", "Gift Appeal", "Gift Amount", "Gift Package", "Gift Pay Method", "Gift Card no/exp", "Gift Reference", "Gift Acknowlege", "Gift Letter", "Gift Receipt", "Gift Attr Source Organisation", "Gift Attr Gift Donation Type", "Gift Attr In Memory of:", "Declaration Start Date", "Declaration Made", "Declaration Tax Payer Status", "Declaration Indicator", "Declaration Source", "Motivation_for_Support ID(Individual)", "Email Consent", "Mphone Consent", "SMS Consent", "Gift Attr In Memory of 2")

End Sub

Sub Add_Rows_Data()


LastRow = imprt.Cells(1, 1).End(xlDown).Row
'How to check if a column exists in the sheet, will throw an error if it doesn't exist
Set imprt = ThisWorkbook.Sheets("Sheet5")
LastRow = imprt.Cells(1, 1).End(xlDown).Row

Bank = WorksheetFunction.Match("Gift Bank Name", imprt.Cells(1, 1).EntireRow, 0)


ConsCode = WorksheetFunction.Match("Cons Code", imprt.Cells(1, 1).EntireRow, 0)
GiftType = WorksheetFunction.Match("Gift Type", imprt.Cells(1, 1).EntireRow, 0)
GiftSubType = WorksheetFunction.Match("Gift Subtype", imprt.Cells(1, 1).EntireRow, 0)
GiftDate = WorksheetFunction.Match("Gift Date", imprt.Cells(1, 1).EntireRow, 0)
GiftCampaign = WorksheetFunction.Match("Gift Campaign", imprt.Cells(1, 1).EntireRow, 0)
GiftFund = WorksheetFunction.Match("Gift Fund", imprt.Cells(1, 1).EntireRow, 0)
GiftAppeal = WorksheetFunction.Match("Gift Appeal", imprt.Cells(1, 1).EntireRow, 0)
GiftAmount = WorksheetFunction.Match("Gift Amount", imprt.Cells(1, 1).EntireRow, 0)
GiftPackage = WorksheetFunction.Match("Gift Package", imprt.Cells(1, 1).EntireRow, 0)
GiftPaymethod = WorksheetFunction.Match("Gift Pay method", imprt.Cells(1, 1).EntireRow, 0)
GiftCardNo = WorksheetFunction.Match("Gift Card no/exp", imprt.Cells(1, 1).EntireRow, 0)
GiftReference = WorksheetFunction.Match("Gift Reference", imprt.Cells(1, 1).EntireRow, 0)
GiftKnowledge = WorksheetFunction.Match("Gift Acknowlege", imprt.Cells(1, 1).EntireRow, 0)
GiftLetter = WorksheetFunction.Match("Gift Letter", imprt.Cells(1, 1).EntireRow, 0)
GiftReceipt = WorksheetFunction.Match("Gift Receipt", imprt.Cells(1, 1).EntireRow, 0)
GiftSourceOrganisation = WorksheetFunction.Match("Gift Attr Source Organisation", imprt.Cells(1, 1).EntireRow, 0)
GiftDonationType = WorksheetFunction.Match("Gift Attr Gift Donation Type", imprt.Cells(1, 1).EntireRow, 0)
GiftInMemory = WorksheetFunction.Match("Gift Attr In Memory of:", imprt.Cells(1, 1).EntireRow, 0)
DeclarationStartDate = WorksheetFunction.Match("Declaration Start Date", imprt.Cells(1, 1).EntireRow, 0)
DeclarationMade = WorksheetFunction.Match("Declaration Made", imprt.Cells(1, 1).EntireRow, 0)
DeclarationTaxPayerStatus = WorksheetFunction.Match("Declaration Tax Payer Status", imprt.Cells(1, 1).EntireRow, 0)
DeclarationIndicator = WorksheetFunction.Match("Declaration Indicator", imprt.Cells(1, 1).EntireRow, 0)
DeclarationSource = WorksheetFunction.Match("Declaration Source", imprt.Cells(1, 1).EntireRow, 0)
Motivation = WorksheetFunction.Match("Motivation_for_Support ID(Individual)", imprt.Cells(1, 1).EntireRow, 0)
GiftAttrInMemoryof2 = WorksheetFunction.Match("Gift Attr In Memory of 2", imprt.Cells(1, 1).EntireRow, 0)
CelebrationType = WorksheetFunction.Match("CelebrationType", imprt.Cells(1, 1).EntireRow, 0)
CelebrationTitle = WorksheetFunction.Match("CelebrationTitle", imprt.Cells(1, 1).EntireRow, 0)
CelebrationDate = WorksheetFunction.Match("CelebrationDate", imprt.Cells(1, 1).EntireRow, 0)
GatewayName = WorksheetFunction.Match("GatewayName", imprt.Cells(1, 1).EntireRow, 0)
Instance = WorksheetFunction.Match("Instance", imprt.Cells(1, 1).EntireRow, 0)
OptInSMSNumber = WorksheetFunction.Match("OptInSMSNumber", imprt.Cells(1, 1).EntireRow, 0)




GiftStatus = WorksheetFunction.Match("Gift Status", imprt.Cells(1, 1).EntireRow, 0)
GiftFrequencyOption = WorksheetFunction.Match("Gift Frequency Option", imprt.Cells(1, 1).EntireRow, 0)
GiftDayofMonth = WorksheetFunction.Match("Gift Day of Month", imprt.Cells(1, 1).EntireRow, 0)
GiftScheduleSpacing = WorksheetFunction.Match("Gift Schedule Spacing", imprt.Cells(1, 1).EntireRow, 0)
Motivation = WorksheetFunction.Match("Motivation_for_Support ID (Individual)", imprt.Cells(1, 1).EntireRow, 0)


DateCreated = WorksheetFunction.Match("DateCreated", imprt.Cells(1, 1).EntireRow, 0)

EmailConsent = WorksheetFunction.Match("Email Consent", imprt.Cells(1, 1).EntireRow, 0)
MphoneConsent = WorksheetFunction.Match("Mphone Consent", imprt.Cells(1, 1).EntireRow, 0)
SMSConsent = WorksheetFunction.Match("SMS Consent", imprt.Cells(1, 1).EntireRow, 0)
GiftAid = WorksheetFunction.Match("GiftAid", imprt.Cells(1, 1).EntireRow, 0)
OptInEmail = WorksheetFunction.Match("OptInEmail", imprt.Cells(1, 1).EntireRow, 0)
OptInSMS = WorksheetFunction.Match("OptInSMS", imprt.Cells(1, 1).EntireRow, 0)
OptInPhone = WorksheetFunction.Match("OptInPhone", imprt.Cells(1, 1).EntireRow, 0)
Amount = WorksheetFunction.Match("Amount", imprt.Cells(1, 1).EntireRow, 0)
TransactionID = WorksheetFunction.Match("TransactionID", imprt.Cells(1, 1).EntireRow, 0)



For x = 2 To LastRow

'imprt.Cells(x, Bank).Value = "HSBC"
imprt.Cells(x, ConsCode).Value = "Guy's & St Thomas' Charity Record"
imprt.Cells(x, GiftType).Value = "Cash"
imprt.Cells(x, GiftAmount).Value = Cells(x, Amount).Value
'imprt.Cells(x, GiftDate).Value = Cells(x, DateCreated).Value
imprt.Cells(x, GiftDate).Value = DateValue(Cells(x, DateCreated).Text)


imprt.Cells(x, GiftAppeal).Value = "22UN01"
imprt.Cells(x, GiftCampaign).Value = "DM"
'imprt.Cells(x, GiftFund).Value = "G00504"
imprt.Cells(x, GiftPaymethod).Value = "Credit Card"
'imprt.Cells(x, GiftType).Value = "Recurring Gift"

'imprt.Cells(x, GiftStatus).Value = "Active"

'imprt.Cells(x, GiftFrequencyOption).Value = "Specific day"
'imprt.Cells(x, GiftDayofMonth).Value = "5"
'imprt.Cells(x, GiftScheduleSpacing).Value = "1"

imprt.Cells(x, GiftKnowledge).Value = "Acknowledged"
imprt.Cells(x, GiftLetter).Value = "Electronic Thank You"
imprt.Cells(x, GiftReceipt).Value = "Receipted"
imprt.Cells(x, GiftSourceOrganisation).Value = "Guy's & St Thomas' Trust Charity"
imprt.Cells(x, GiftDonationType).Value = "Philanthropic"

imprt.Cells(x, GiftSubType).Value = "Online Payments"

imprt.Cells(x, DateCreated).Value = DateValue(Cells(x, DateCreated).Text) 'Date format

'imprt.Cells(x, GiftAttrInMemoryof2).Value = Cells(x, CelebrationType).Value & "-" & Cells(x, CelebrationTitle).Value & "-" & Format(Cells(x, CelebrationDate).Value, "dd-mmm-yy")



imprt.Cells(x, GiftReference).Value = UCase(Cells(x, GatewayName).Value & "_" & Cells(x, TransactionID).Value) 'Cells(x, TransactionID).Value
imprt.Cells(x, Motivation).Value = "Unknown"
imprt.Cells(x, EmailConsent).Value = "Yes_optin"
imprt.Cells(x, MphoneConsent).Value = "Yes_optin"
imprt.Cells(x, SMSConsent).Value = "No_optin"

'OptInSMSNumber
Imprt.Cells(x, OptInSMSNumber).Value =


'imprt.Cells(x, GiftAttrInMemoryof2).Value = Cells(x, CelebrationType).Value & "-" & Cells(x, CelebrationTitle).Value & "-" & Format(Cells(x, CelebrationDate).Value, "dd-mmm-yy")

If Cells(x, CelebrationType).Value <> "" Then
imprt.Cells(x, GiftAttrInMemoryof2).Value = Cells(x, CelebrationType).Value & "-" & Cells(x, CelebrationTitle).Value & "-" & Format(Cells(x, CelebrationDate).Value, "dd-mmm-yy")

End If

'Gift Aid Declaration
If UCase(Cells(x, GiftAid)) = "YES" Then
imprt.Cells(x, DeclarationStartDate).Value = "2000/04/06"
imprt.Cells(x, DeclarationMade).Value = Cells(x, DateCreated).Value
imprt.Cells(x, DeclarationTaxPayerStatus).Value = "Active"
imprt.Cells(x, DeclarationIndicator).Value = "Electronic"
imprt.Cells(x, DeclarationSource).Value = "Guy's & St Thomas' Trust Charity"
ElseIf Cells(x, GiftAid) = "Test" Then
imprt.Cells(x, DeclarationStartDate).Value = "2000/04/06"
imprt.Cells(x, DeclarationMade).Value = Cells(x, DateCreated).Value
imprt.Cells(x, DeclarationTaxPayerStatus).Value = "Active"
imprt.Cells(x, DeclarationIndicator).Value = "Electronic"
imprt.Cells(x, DeclarationSource).Value = "ASDF123"
Else
imprt.Cells(x, DeclarationSource).Value = ""
End If

'Donor Email Consent

If (Cells(x, OptInEmail)) = "TRUE" Then
imprt.Cells(x, EmailConsent).Value = "Yes_optin"
Else
imprt.Cells(x, EmailConsent).Value = "No_optin"

End If


'Donor OptInSMS

If (Cells(x, OptInSMS)) = "TRUE" Then
imprt.Cells(x, SMSConsent).Value = "Yes_optin"
Else
imprt.Cells(x, SMSConsent).Value = "No_optin"

End If

'Donor OptinPhone

If (Cells(x, OptInPhone)) = "TRUE" Then
imprt.Cells(x, MphoneConsent).Value = "Yes_optin"
Else
imprt.Cells(x, MphoneConsent).Value = "No_optin"

End If


Next x

End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,159
Office Version
  1. 365
Platform
  1. Windows
So where exactly is the source data coming from?
Is it already in Excel, and just being copied over?
If so, what is the format/structure of that Mobile phone number field on the source file?

And what happens if you try pre-formatting the column you are pasting the data to as Text before entering the data?
 

vbanewbie68

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

I download a report from a platform called Go Donate that is linked to our website's donation page. I use VBA to open a file to that report and then it generates to add new columns. From there to upload it onto our database using import tool called importomatic with RaisersEdge.
Would you like to see download report?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,159
Office Version
  1. 365
Platform
  1. Windows
I download a report from a platform called Go Donate that is linked to our website's donation page. I use VBA to open a file to that report and then it generates to add new columns. From there to upload it onto our database using import tool called importomatic with RaisersEdge.
Would you like to see download report?
What format is the report downloaded in?
When you view the raw downloaded report, what program do you use to view it, and what format do the mobile numbers look to be in on that downloaded report?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,159
Office Version
  1. 365
Platform
  1. Windows
There is an issue with opening CSV files directly in Excel. Excel performs its own automatic "conversions" on the data, based on what it thinks the data is.
If it sees a big long string of numbers, it is going to incorrectly determine that it should be opened as a number, and that will drop leading zeroes and use scientific notation, as you are seeing.

I assume that you are manually opening this downloaded CSV file before running the macro?
I say that because I do not see anywhere in your code where the CSV file is being opened.
I think you need to change how the CSV file is being opened, so it does not perform that automated data conversion on the mobile phone number column.
 

Forum statistics

Threads
1,148,397
Messages
5,746,457
Members
424,020
Latest member
LongDoo

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
Top