Hello,
I am using the following function to combine the account # with the location ID, but somehow the function is creating a space on some of the records, why?
This is the formula I am using, which is working for most of the records, however there are some that contain a space in between.
=Concat([BAccount.AcctCD],[Location.LocationCD])
Please see attached screenshot.
Best answer by darylbowman
View original
Hi
+2
They may be fixed width in the database, and such having the space.
You could either Trim, or replace a space with a blank.
=Concat(Trim([BAccount.AcctCD]),Trim([Location.LocationCD]))
or
=Replace(Concat([BAccount.AcctCD],[Location.LocationCD]),’ ‘,’’)
+17
You can use the TRIM() function to remove the space for those texts. like below.
=Concat(Trim([BAccount.AcctCD]),[Location.LocationCD])
Great guys,
The =Concat(Trim([BAccount.AcctCD]),Trim([Location.LocationCD])) got rid of the spaces.
One more thing, how can I use the Replace fuction in this same formula? I need to also replace the text MAIN with 000. See below.
Where should I insert the Replace command?
=Concat(Trim([BAccount.AcctCD]),Trim([Location.LocationCD]))
=Replace([Location.LocationCD], 'MAIN', '000')
+11
=Concat(Trim([BAccount.AcctCD]),Replace(Trim([Location.LocationCD]),'MAIN','000')
This one seemed to have done the trick.
Thank you all!!
=Concat(trim([BAccount.AcctCD]),Replace([Location.LocationCD], 'MAIN', '000'))
+11
This one seemed to have done the trick.
=Concat(trim([BAccount.AcctCD]),Replace([Location.LocationCD], 'MAIN', '000'))
This is not going to trim the LocationCD.
This one seemed to have done the trick.
=Concat(trim([BAccount.AcctCD]),Replace([Location.LocationCD], 'MAIN', '000'))
This is not going to trim the LocationCD.
I see, maybe in my case, it didn’t need to be, as the data showed correctly after without the spaces. So I would assumed the AcctCD was the one needing Trimming.
Thank you!
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.