Solved

MS Excel

Posted on 2015-02-23
11
63 Views
Last Modified: 2015-02-24
I create a workbook with data imported from Access. One of the fields has 13 digits in it that are in the text data type. I wish to put a - after the first five, then after the next five, followed by the last three. I have tried an input mask,  and it will not work with text. So I am thinking an Array formula might work, but I have no idea how to create it. This way I can create a template for the end user - so the 13 digits will import like this 12345-67890-123. Can anyone help with this? I have tried everything. Wheen I create the input mask in the Access query it shows up properly - but on import it shows up without the dashes.

Please help

Thank you
0
Comment
Question by:dawber39
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40627063
use something like this in the query as one of the column

select Left([FieldName], 5) & "-" & Mid([Fieldname],6,5) & "-" & Right([FieldName],3) As whatever
from tableName
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40627070
Try this formula

Concatinate(mid(a1,1,5),"-",mid(a1,6,5),"-",mid(a1,11,3))

I,m using my mobile and sorry for any typing mistakes. The idea is using concatinate and mid functions
0
 

Author Comment

by:dawber39
ID: 40627362
Rey - Ive done something like that prior -  but it still imports to excel as the whole thing without dashses - thats why it needs to be on the excel end. If it were a number field  - it would be easy... but thaank you for the suggestion. Far west - I will try that tomorrow
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40627374
<Rey - Ive done something like that prior -  but it still imports to excel as the whole thing without dashses ->
 impossible, maybe you have not done it properly..
how are you importing to excel?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 40627588
In your query, use this expression for the offending value:

TroubleField: Format([YourPlainNumberField], "\'@@@@@\-@@@@@\-@@@")

It prefixes a single quote which forces Excel to read it as text.

/gustav
0
 

Author Comment

by:dawber39
ID: 40628472
I will try the other suggestions - The Concatenation formula does not work

Attached is a spreadsheet with the original item number - and the way I want the dashes to show in Excel

Thank you for your input people
Dash-Test.xlsx
0
 
LVL 12

Expert Comment

by:FarWest
ID: 40628483
here is the excel file with formula
0
 
LVL 12

Accepted Solution

by:
FarWest earned 500 total points
ID: 40628486
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40628501
where is your access db?
0
 

Author Closing Comment

by:dawber39
ID: 40628591
Awesome - thank you for all your input - but this formula works. You people are awesome up here.
0
 

Author Comment

by:dawber39
ID: 40629745
I will be posting anothe question with regards to this - there seems to be another issue that was not taken into account. The formula works in a field that is not imported. The query that separates the field in 3 sections with dashes - runs perfectly in Access - but the import loses that format - but we'll leave that for another question.

Thanks again guys
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question