Solved Formatting Some Text

[EzGoingKev]

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)

 

[theDBguy]

Hi. You could try something like:

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

Hope that helps...

 

[EzGoingKev]

Hi. You could try something like:

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!

 

[theDBguy]

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.

 

[Isaac]

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.