Lookup field

[huntersoasis]

I currently have a field in table 1 that is set to lookup "combo box", The list is in Table 2.

The issue is all works except the data shows up as

x-small|, small|, medium|, large|

The data should show up as


x-small|small|medium|large|

How to i get the Comma and the space to be removed.

Or change the Comma to |

 

[elmer007]

I think the SUBSTITUTE formula may be what you're looking for: =SUBSTITUTE(A1,",","|")
change A1 to the appropriate cell in your spreadsheet.

Or: =SUBSTITUTE(A1,",","") to replace the comma with nothing.

 

[huntersoasis]

I guess let me clarify what I have in simple form and what is needed and lets see what suggestions we get.


Table 1
ID, Name, Size

Table 2
ID, Sizes

From1

I need the Size to be check boxes of the size opens avaiable in Table2; Sizes when option is check it is added to Table1;Size but in a format that may be
If all check boxes clicked
Small|Medium|Large
or
If only small and medium are checked
Small|Medium
If only small and medium are checked
Small|Medium



The real one has almost 50 options so im doing this as a simple example.
Thanks for the help

 

[Micron]

You posted in the Access section but it looks like you got a response for Excel and didn't comment on that. So I don't know which you want and your second post is not clear either. It sounds like you want to use a table lookup field to retrieve values from a second table, but doing this only allows you to choose one value, not create a concatenated string. If that's what your're doing, take a look here to see if it helps https://support.microsoft.com/en-us/kb/304462

If you're not doing table lookups, then your second post is too confusing for me. Sorry.