Use =Left & =Right together

[amerifax]

I need to drop the first and last letter. Which I can do but not as slick as I would like to.

=LEFT(A1, LEN(A1)-1)
=RIGHT(A1, LEN(A1)-1)

I do this one at a time. I don't know who to run this formula in one cell.

I have two cells with the same type of problem.
A1
"AUDS"
A2
"Accessibility & Universal Design Services"

I would like them to end up in A3 as Below:
AUDS,Accessibility & Universal Design Services

Note: The two values have a "," between them.

 

[Peter_SSs]

Edited:

This should help with dropping end characters:
=MID(A1,2,LEN(A1)-2)

Though this may be what you actually want:
=SUBSTITUTE(A1&","&A2,"""","")

 

[amerifax]

Edited:

This should help with dropping end characters:
=MID(A1,2,LEN(A1)-2)

Though this may be what you actually want:
=SUBSTITUTE(A1&","&A2,"""","")

Hi Peter,
It work fine. I was hoping to run both cells with one formula. One value is in A1 the other is in B1.

A1
"AUDS"
B1
"Accessibility & Universal Design Services"

=MID(A1,2,LEN(A1)-2)
=MID(B1,2,LEN(B1)-2)

I just don't know how to tag the two together. I know that if a put them into two seperate cells then:
=MID(A1,2,LEN(A1)-2) put value into C1
=MID(B1,2,LEN(B1)-2) put value into D1

=SUBSTITUTE(C1&","&D1,"""","") put value into E1

I was hoping to put the "MID" command into one cell. if possable. This was I only have to run one last part and that is to SUBSTITUTE.

Bob

 

[Peter_SSs]

There seems to be some confusion where the two original cells are. In your first post, you said A1 and A2. Now you appear to be saying A1 and B1.

So, if it is A1 and B1, doesn't my second formula, adjusted to ...
=SUBSTITUTE(A1&","&B1,"""","")
... do what you want in one go?

Excel Workbook
	A	B	C
1	"AUDS"	"Accessibility & Universal Design Services"	AUDS,Accessibility & Universal Design Services
2
Sheet5

 

[amerifax]

I'm sorry. I saw the mistake while posting my replay. I WAS THINKING I SHOULD MENTION THE MISTAKE. I NOW KNOW I SHOULD HAVE MENTIONED IT.

Your solution is perfect.
Thanks for the help.

I also noticed post Extracting numbers and text I find this of great interest also.

Bob

 

[amerifax]

There is two parts to the "MID". One value with " in cell A1. Also B2 has the " that has to be removed. Is it possable to take them both out with one formula. If you have shown that to me. I must be missing it.

Or is trhere no way to do it. My problem is creating a formula that can effect both cells at the same time, removing the " from the front and back of both cells.

Bob

 

[jeffreybrown]

It seems like Peter's post#4 answers the question, but are you searching for =MID(A1,2,LEN(A1)-2)&", " &MID(B1,2,LEN(B1)-2)

 

[amerifax]

Yes I am. It's the &", " & that brings them together.

Thanks to you and Peter.

Bob

 

[Peter_SSs]

It's the &", " & that brings them together.

That's right, but if all the values you are dealing with (you only gave one example) start and finish with a " and that is what you are trying to remove while joing the values, then surely

=SUBSTITUTE(A1&","&B1,"""","")

does the job in a much simpler way?