Aligning two lists in Calc problem fixed by Gilberto

[stuarts.burgers]

Hi, I have two lists from different sources that I need to make sure that the contents of the lists are positioned in the same place within their cell.
street names.ods

In the above example, I need the contents “Green Cells” to be positioned in exactly the same position within their cell as the contents of the “Yellow Cells”.

I have attempted to add or subtract spaces but to no avail.

What I am attempting to do is use Matching formula
“=MATCH(E2&D2,$‘Mailman’‘s Route’.A$1:A$538&$‘Mailman’‘s Route’.B$1:B$538,0)” where the “Green Cells” are E and the “Yellow Cells” are $‘Mailman’s Route’ .A$1:A$538.

The other part of formula works, if I cut and paste from “E” into “A$” and I can get a Match to work without doing anything to the “D” and “B$” cells.

The Yellow List is taken from a database and arrives to me an Excel Sheet that I open in Libre Calc

As I have 16 Green List and up to 33 Yellow Lists per Green Lists I really need to automate.

I have done the cut and past thing as a workaround but I would like to automate the process as I do not have the time to do cutting and pasting.

Thanks
Stuart

[anon73440385]

I don’t understand at all but in your example the strings are different due to one or more “space” characters at the end of the strings in the “Yellow Cells” and thus they do not align to the same posiition like the same word (not having these extra space characters) in the “Green Cells”. So is it really just about to get rid of the extra spaces at the end of the strings? If yes - then imo the summary of this question is completely misleading.

[schiavinatto]

Select the entire area (A1: E21)

Edit / Find & Replace

in Find “type two spaces”

in Replace “leave blank”

click [Replace All]

---

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball to the left of the answer, to finish the question.

[stuarts.burgers]

Giberto Thank you it works brilliantly, thank you for your input and wisdom, you have just saved me a huge amount of time. I knew the problem was with the spacing but I could no see how to fix it

[schiavinatto]

Ok @stuarts.burgers, I’m glad to have helped, if you have more questions, just post here. we’re on duty.

Ok @stuarts.burgers , fico contente em ter ajudado, se tiver mais dúvidas, é só postar aqui. estamos de plantão.