Using Mid in a string

[BonnieG]

I'm trying to get a value from a spreadsheet to import into my MS Access database. Currently I am trimming the spaces/carriage returns from it but need to strip some more data from the value.

Here is my code.

trimmed_department = Trim(Replace(new_department, vbCrLf, ""))

Example value being "123 Point 5 Finance and Accounting"

I want to use Mid (I think?) to remove the "123 Point 5" (it is always the same with no exceptions) but don't know how to use it as I don't know how to use multiple parameters within a string.

Any help appreciated as always.

 

[pr2-eugin]

Mid function help would help you get started.

? Mid("123 Point 5 Finance and Accounting", 13)
Finance and Accounting

You can also use Right:

? Right("123 Point 5 Finance and Accounting", Len("123 Point 5 Finance and Accounting")-12)
Finance and Accounting

 

[BonnieG]

Thanks Paul.

Am I right in thinking it would look something like this? As this is returning a null value at the moment...

trimmed_department = Mid(Trim(Replace(new_department, vbCrLf, "")), 13)

 

[pr2-eugin]

What does the Trim(Replace(new_department, vbCrLf, "")) return?

 

[BonnieG]

The full department name (123 Point 5 Finance and Accounting).

 

[pr2-eugin]

Then it should return the desired value. Mid always returns a String. Check the data again !

 

[BonnieG]

Hmmm.

This returns "123 Point 5 Finance and Accounting":

trimmed_department = Mid(Trim(Replace(new_department, vbCrLf, "")), 1)

This returns nothing:

trimmed_department = Mid(Trim(Replace(new_department, vbCrLf, "")), 2)

I'm at a loss!

 

[namliam]

new_department = "123 Point 5 Finance and Accounting"
?Mid(Trim(Replace(new_department, vbCrLf, "")), 2)
23 Point 5 Finance and Accounting
?Mid(Trim(Replace(new_department, vbCrLf, "")), 13)
Finance and Accounting

 

[BonnieG]

Ignore me - I'm an idiot. I was doing an If/Else/EndIf and only including the values which matched a certain department.

Obviously, all of those would now be the department name MINUS the leading characters, argggggggggggggggg!

It's always nice discovering errors but does tend to make you feel like a bit of a plonker.

We are up and running. Thank you Paul.