Solved Formatting Some Text

EzGoingKev

Registered User.
Local time
Today, 06:19
Joined
Nov 8, 2019
Messages
199
I have some data in a table that is listed like this:
NTC10RM
NTC5RM
TC10RM
TC5RM

I need them to be like this:
NTC-10-RM
NTC-5-RM
TC-10-RM
TC-5-RM

For the NTC/TC part I used LEFT([FieldName], InStr([FieldName], "C")) & "-". For the RM part I know I could use some like RIGHT([FieldName],2).

The part that I do not know how to handle is the numeric part because some have one number and some have two.

I am looking to write something like : Formatted : LEFT([FieldName], InStr([FieldName], "C")) & "-" & <part I need help with> & "-" & RIGHT([FieldName],2)
 
Hi. You could try something like:
Code:
Val(Mid([FieldName],InStr([FieldName],"-")+1))
Hope that helps...
 
Hi. You could try something like:
Code:
Val(Mid([FieldName],InStr([FieldName],"-")+1))
Hope that helps...
The original data does not contain a dash so I took your code and modified it to:

Val(Mid([FieldName],InStr([FieldName],"C")+1))

and it worked great.

Thanks for your help!
 
The original data does not contain a dash so I took your code and modified it to:

Val(Mid([FieldName],InStr([FieldName],"C")+1))

and it worked great.

Thanks for your help!
Hi. You're welcome. Glad to hear you got it to work. Good luck with your project.
 
It sounds like you may have an overloaded attribute ... and may be well worth it to re-design the way data flows into the table to avoid this, using 3 columns instead of 1. Maybe, maybe not - just a thought.
 

Users who are viewing this thread

Back
Top Bottom