Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Space after Mid Function
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Space after Mid Function
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Space after Mid Function
Try
=MID(Trim(F1),5,30) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "David" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Space after Mid Function
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Space after Mid Function
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Space after Mid Function
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Space after Mid Function
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" wrote in message ... 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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Space after Mid Function
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! |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Space after Mid Function
Nice one, Chip! I haven't seen that addin before now. Clever idea, useful
result. Best wishes Harald "Chip Pearson" skrev i melding ... This would be a situation in which my CellView addin would be helpful. http://www.cpearson.com/excel/cellview.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
VBA | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |