How can I quickly navigate to a folder path that I have on the clipboard in Open File dialogs?

Shift Command G in the Finder. Here you can copy and paste complete path:

This is a short cut from the drop down:

Finder>Go>Go to Folder

Get to know the Finder on your Mac - Apple Support

See and organize your files in the Finder on Mac - Apple ...

Use the Dock on Mac - Apple Support

As others have commented there's nothing that will exactly mirror your experience on Windows.

However there are alternatives.

1 - When in an open-save dialog box, go back to the Finder and drag the file or folder you want to go to, into the open-save dialog box - it will then jump to that file or folder.

2 - There's an app called 'Default Folder X' that adds lots of options to open-save dialog boxes, one of which is a drop down that shows your current open Finder windows, which I find very useful.

The NSOpenPanel framework behind the Open/Save panels of applications on macOS has no provision for pasting a file or folder path from the clipboard and act on that path. That is why these panels offer different panel views for direct selection of files and folders.

If you want to go to a folder path on macOS, then use the Finder's available Go To Folder (shift+command+G), and paste the folder path in that dialog.

The POSIX style path that you illustrated works in the second paragraph solution, but otherwise has no meaning to the Finder, but does in the Terminal.

Hi.

Is this what you're looking for?:

Option+Right-click on the file or folder you wish to copy the path...

then select Copy "filename/foldername" as Pathname.

From there you'll have the path in the clipboard and you can paste it wherever you need it.

I'd screenshot it for you, but the Option key is otherwise engaged when trying to demo this. 😉