do not find same result

[majidsiddique]

hi,
i have problem in Cell Value:
value of A1 cell:
[TABLE="width: 348"]
<tbody>[TR]
[TD="width: 348"]MD-5001219 - onyx - surgeryitem - 00016220228000
i use formula:
=LEFT(A1,FIND(" ",A1)-1)
result ok if change the cell value
but in
=RIGHT(A1,FIND(" ",A1)-1)
result would changed when i change the value of cell, why?
can anybody tell me.[/TD]
[/TR]
[TR]
[TD="width: 348"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

What are you trying to get from the value in cell A1 ?

 

[majidsiddique]

simply to check both formula works same or not.
=LEFT(A1,FIND(" ",A1)-1)
result=[TABLE="width: 173"]
<tbody>[TR]
[TD="width: 173"]MD-5001219 OK
=RIGHT(A1,FIND(" ",A1)-1)
[TABLE="width: 173"]
<tbody>[TR]
[TD="width: 173"]result:
6220228000
result shoud be like this = 00016220228000
kindly explaine me this[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

00016220228000 is a number.

Leading zeroes are dropped in Excel
To maintain the zeroes you'd have to do this

=TEXT(RIGHT(A1,FIND(" ",A1)-1),"00000000000000")

 

[steve the fish]

This:

=RIGHT(A1,FIND(" ",A1)-1)

wont work for what you want. Try:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

 

[majidsiddique]

thanks to Both,
Specially Steve Thanks
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))
this works properly.

=TEXT(RIGHT(A1,FIND(" ",A1)-1),"00000000000000")
in this, i increase or decrease the value of last ZERO result same = [TABLE="width: 173"]
<tbody>[TR]
[TD="class: xl65, width: 173"]00006220228000[/TD]
[/TR]
</tbody>[/TABLE]

 

[majidsiddique]

Hi steve,special K-99 and all,
can you tell me general format of formula to detect value in inverted comma mentio " " any value of string
like "onyx" exclude remaining text print then change value of in "surgeryitem" remaining text print.
print as my wish exclude inverted comma,
Thanks.

 

[steve the fish]

Maybe try to explain what you are after by giving examples of what you have and then what you think the result should be.

 

[majidsiddique]

very simple sir,
you insert any value of given TEXT in " " exclude from original TEXT:
eg:
MD-5001219 - onyx - surgeryitem - 00016220228000
"onyx" exclude remaining part of TEXT MD-5001219 - surgeryitem - 00016220228000
change i want insert in:
"5001219" exclude, remaining part of TEXT MD- - onyx - surgeryitem - 00016220228000
space also include in.

 

[steve the fish]

This works provided none of the 'words' have spaces:

=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1,"-"," "),B1,""))," "," - ")

where A1 contains the long string and B1 contains the string to exclude.