Space after Mid Function

[David]

I am using mid and then concatenate to get the text I need for another
workbook. However, there is a space being inserted from the mid function, so
that when I use concatenate, an extra space appears. Here are the two
functions, they are in side by side columns.
=MID(F1,5,30) 'this is producing a space at the end. To test, copy the
result and paste special values.
=CONCATENATE(G1,"-",C1) is in the next column.
This is the result I get after using both:
Austin -TX 'The space is before the -

Thanks...this one I just can't figure!

[Chip Pearson]

David,

The MID function doesn't insert a space. Examine your cells very
carefully to see if there is a space where you aren't seeing it.
This would be a situation in which my CellView addin would be
helpful.

http://www.cpearson.com/excel/cellview.htm


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"David" <David@discussions.microsoft.com> wrote in message
news:B20D0063-F54B-4ED7-978F-FF16FFFFEEFD@microsoft.com...
>I am using mid and then concatenate to get the text I need for
>another
> workbook. However, there is a space being inserted from the mid
> function, so
> that when I use concatenate, an extra space appears. Here are
> the two
> functions, they are in side by side columns.
> =MID(F1,5,30) 'this is producing a space at the end. To test,
> copy the
> result and paste special values.
> =CONCATENATE(G1,"-",C1) is in the next column.
> This is the result I get after using both:
> Austin -TX 'The space is before the -
>
> Thanks...this one I just can't figure!
>
>

[Bob Phillips]

Try

=MID(Trim(F1),5,30)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"David" <David@discussions.microsoft.com> wrote in message
news:B20D0063-F54B-4ED7-978F-FF16FFFFEEFD@microsoft.com...
> I am using mid and then concatenate to get the text I need for another
> workbook. However, there is a space being inserted from the mid function,
so
> that when I use concatenate, an extra space appears. Here are the two
> functions, they are in side by side columns.
> =MID(F1,5,30) 'this is producing a space at the end. To test, copy the
> result and paste special values.
> =CONCATENATE(G1,"-",C1) is in the next column.
> This is the result I get after using both:
> Austin -TX 'The space is before the -
>
> Thanks...this one I just can't figure!
>
>

[Stefi]

Hi David,

You don't tell us the content of F1 that definitely affect hte result of
MID. Nevertheless =TRIM(MID(F1,5,30)) will remove the extra space.

Regards,
Stefi


„David” ezt �*rta:

> I am using mid and then concatenate to get the text I need for another
> workbook. However, there is a space being inserted from the mid function, so
> that when I use concatenate, an extra space appears. Here are the two
> functions, they are in side by side columns.
> =MID(F1,5,30) 'this is producing a space at the end. To test, copy the
> result and paste special values.
> =CONCATENATE(G1,"-",C1) is in the next column.
> This is the result I get after using both:
> Austin -TX 'The space is before the -
>
> Thanks...this one I just can't figure!
>
>

[bpeltzer]

MID won't tack on a space, but if the 34th character of the string in F1 is a
space, then that will be the final character returned by the MID function
with the arguments you gave it. Either take one less character,
=mid(f1,5,29), or use the TRIM function to remove leading and trailing
spaces, =trim(mid(f1,5,30))

"David" wrote:

> I am using mid and then concatenate to get the text I need for another
> workbook. However, there is a space being inserted from the mid function, so
> that when I use concatenate, an extra space appears. Here are the two
> functions, they are in side by side columns.
> =MID(F1,5,30) 'this is producing a space at the end. To test, copy the
> result and paste special values.
> =CONCATENATE(G1,"-",C1) is in the next column.
> This is the result I get after using both:
> Austin -TX 'The space is before the -
>
> Thanks...this one I just can't figure!
>
>

[WillR]

Are you sure you're not just grabbing an extra char...

try

=MID(F1,5,29)
--
Kind Regards,
Will Riley


"David" wrote:

> I am using mid and then concatenate to get the text I need for another
> workbook. However, there is a space being inserted from the mid function, so
> that when I use concatenate, an extra space appears. Here are the two
> functions, they are in side by side columns.
> =MID(F1,5,30) 'this is producing a space at the end. To test, copy the
> result and paste special values.
> =CONCATENATE(G1,"-",C1) is in the next column.
> This is the result I get after using both:
> Austin -TX 'The space is before the -
>
> Thanks...this one I just can't figure!
>
>

[David]

Simple as that Bob...thank you very much!!

"Bob Phillips" wrote:

> Try
>
> =MID(Trim(F1),5,30)
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "David" <David@discussions.microsoft.com> wrote in message
> news:B20D0063-F54B-4ED7-978F-FF16FFFFEEFD@microsoft.com...
> > I am using mid and then concatenate to get the text I need for another
> > workbook. However, there is a space being inserted from the mid function,
> so
> > that when I use concatenate, an extra space appears. Here are the two
> > functions, they are in side by side columns.
> > =MID(F1,5,30) 'this is producing a space at the end. To test, copy the
> > result and paste special values.
> > =CONCATENATE(G1,"-",C1) is in the next column.
> > This is the result I get after using both:
> > Austin -TX 'The space is before the -
> >
> > Thanks...this one I just can't figure!
> >
> >
>
>
>

[David]

Thanks to all for the very prompt and solution filled responses!!

"WillR" wrote:

> Are you sure you're not just grabbing an extra char...
>
> try
>
> =MID(F1,5,29)
> --
> Kind Regards,
> Will Riley
>
>
> "David" wrote:
>
> > I am using mid and then concatenate to get the text I need for another
> > workbook. However, there is a space being inserted from the mid function, so
> > that when I use concatenate, an extra space appears. Here are the two
> > functions, they are in side by side columns.
> > =MID(F1,5,30) 'this is producing a space at the end. To test, copy the
> > result and paste special values.
> > =CONCATENATE(G1,"-",C1) is in the next column.
> > This is the result I get after using both:
> > Austin -TX 'The space is before the -
> >
> > Thanks...this one I just can't figure!
> >
> >

[Harald Staff]

Nice one, Chip! I haven't seen that addin before now. Clever idea, useful
result.

Best wishes Harald

"Chip Pearson" <chip@cpearson.com> skrev i melding
news:uAzWonXsGHA.4252@TK2MSFTNGP02.phx.gbl...

> This would be a situation in which my CellView addin would be
> helpful.
>
> http://www.cpearson.com/excel/cellview.htm
>