MS Excel

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
dawber39Asked:
Who is Participating?
 
FarWestConnect With a Mentor Commented:
0
 
Rey Obrero (Capricorn1)Commented:
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
 
FarWestCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
dawber39Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
<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
 
Gustav BrockCIOCommented:
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
 
dawber39Author Commented:
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
 
FarWestCommented:
here is the excel file with formula
0
 
Rey Obrero (Capricorn1)Commented:
where is your access db?
0
 
dawber39Author Commented:
Awesome - thank you for all your input - but this formula works. You people are awesome up here.
0
 
dawber39Author Commented:
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
All Courses

From novice to tech pro — start learning today.